How to select rows that do not matter in the second table

Question

Basically, I have a main table (accounts) and a meta table (accounts_meta) ... The meta table looks like this:

id | account_id | meta_key | meta_value

What I want to do is select only accounts that do not have "referrer_paid" as a row in the account_meta table ...

Here is my code so far ...

SELECT a.* FROM accounts AS a
    LEFT JOIN accounts_meta AS am ON a.id = am.account_id AND am.meta_key != 'referrer_paid'
    WHERE a.account_referrer != ''
    GROUP BY a.id

Hope I make sense. What am I doing wrong?

Answer 1

tiny change from @lexu:

SELECT * 
  FROM accounts 
 WHERE id NOT IN (select account_id 
                     from `account_meta_table` 
                    where meta_key = 'referrer_paid'
                  );

Answer 2

SELECT * 
  FROM accounts 
 WHERE id NOT IN ( select DISTINCT account_id 
                     from `account_meta_table` 
                    where meta_key != 'referrer_paid'
                  );

Answer 3

SELECT a.* FROM accounts AS a
LEFT JOIN accounts_meta AS am ON a.id = am.account_id AND am.meta_key = 'referrer_paid'
WHERE a.account_referrer != ''
  AND am.account_id IS NULL

, left-join-is-null

EDIT: duh, am.meta_key != 'referrer_paid' am.meta_key = 'referrer_paid'

This is what you wanted. It returns NULL for the concatenated string if it does not match, and you only accept NULL strings

Answer 4

CHOOSE a. * FROM accounts AS a LEFT JOIN accounts_meta AS am ON a.id = am.account_id AND am.meta_key! = 'Referrer_paid' WHERE ISNULL (am.account_referrer) GROUP BY a.id