CODEWITHSUNDEEP
mysql
Ben Sinclair
Ben Sinclair

Reputation: 3986

How to select rows that don't have value in a second table

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 only select accounts that do not have 'referrer_paid' as a row in the accounts_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

Hopefully I am making sense. What am I doing wrong?

Upvotes: 4

Views: 2282

Answers (4)

Imre L
Imre L

Reputation: 6249

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

you dont need group by as left-join-is-null don't produce duplicate account rows

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

This is what you wanted. It returns NULL for joined row if it doesnt match and you only take NULL rows

Upvotes: 2

didxga
didxga

Reputation: 6125

tiny change from @lexu:

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

Upvotes: 4

bogdan
bogdan

Reputation: 671

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 ISNULL(am.account_referrer) GROUP BY a.id

Upvotes: 0

Salil
Salil

Reputation: 47532

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

Upvotes: 4

Related Questions