CODEWITHSUNDEEP
mysqlsql
Dennais
Dennais

Reputation: 506

MYSQL - Find records from one table which don't exist in another

I've got the following two SQL tables (in MySQL):

Users | id | name | |----|------| | 1 | Luke | | 2 | Mark | | 3 | Lucy | | 4 | Biff |

User category | user_id | category_id | |---------|-------------| | 1 | 5 | | 1 | 6 | | 2 | 5 | | 2 | 7 | | 3 | 5 |

I want users that are in User category but not if category id is 6.

In this case Mark and Lucy because Luke is in category 6 too and Biff has no category.

There is a way to do it without subquery and only in one query?

Upvotes: 0

Views: 104

Answers (2)

num8er
num8er

Reputation: 19372

Join them and check for difference :

SELECT * FROM users 
INNER JOIN user_category ON (user_category.user_id = users.id) 
WHERE user_category.category_id <> 6

p.s. using group by is not effective, cuz it says to DB engine to do additional group by operation after gathering data.

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You can group by user_id and eliminate those rows where there is atleast one category_id of 6.

select uc.user_id,u.name
from user_category uc
join users u on uc.user_id = u.id
group by uc.user_id,u.name
having sum(case when category_id = 6 then 1 else 0 end) = 0

Upvotes: 4

Related Questions