CODEWITHSUNDEEP
mysqlsql
Adam
Adam

Reputation: 3128

Getting all records in many-to-many relationship even if the relationship is missing

I have a user table, group table, and user_group table for many to many.

I am confused on how I can get the list of all users belonging to a specific group and if the user does not belong to the current group, I still want his record listed with the group field set to NULL.

Upvotes: 1

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If I understand correctly, you can do:

select u.*, ug.group_id
from users u left join
     user_group ug
     on ug.user_id = u.user_id and ug.group_id = <the group>;

Upvotes: 3

Uueerdo
Uueerdo

Reputation: 15941

If you do not care about including a group without users, this should work; all groupless users should be collected together under the NULL group_id's userIDs.

SELECT g.group_id, GROUP_CONCAT(u.user_id) AS userIDs
FROM `users` AS u
LEFT JOIN `user_group` AS ug ON u.user_id = ug.user_id
LEFT JOIN `group` AS g ON ug.group_id = g.group_id
GROUP BY g.group_id
;

Upvotes: 0

Related Questions