CODEWITHSUNDEEP
mysql
Nicole
Nicole

Reputation: 1

Select data based on data in another table

I have 2 tables:

|id| username     |
-------------------
|1 |John Doe      |
|2 |Jane Doe      |
|3 |Luke Skywalker|

|id|user_id|group_id|
---------------------
|1 |1      |5       |
|2 |1      |6       |
|3 |2      |6       |

I am trying to filter out users already in a group so the same user can not be added twice to the same group.

How can I select let's say all users from table1 who are not in group_id = 5 in table2?

Upvotes: 0

Views: 107

Answers (3)

Michiel van Vaardegem
Michiel van Vaardegem

Reputation: 2035

SELECT u.*
FROM table1 u
WHERE NOT EXISTS (
    SELECT 1
    FROM table2 g
    WHERE g.user_id = u.id AND g.group_id = 5
)

Upvotes: 2

redShadow
redShadow

Reputation: 6777

You can use:

SELECT * FROM table1 WHERE id NOT IN (SELECT user_id FROM table2 WHERE group_id=5)

But, to ensure uniqueness, you'd better add a UNIQUE KEY on (user_id, group_id) in table2:

ALTER TABLE table2 ADD UNIQUE KEY unique_user_group (user_id, group_id);

this way, MySQL itself won't accept duplicate records, without worring to check each time on the application side.

Upvotes: 2

Robert
Robert

Reputation: 3074

You'd need to use a join clause with a <> in the where clause. Assuming they are joined on user_id this should work.

This will give you results from both tables without the ones in table 2 that are = 5.

SELECT  *
  FROM   table1 t1
  INNER JOIN table2 t2 ON t2.user_id = t1.id
   WHERE   t2.group_id <> 5

Upvotes: 1

Related Questions