CODEWITHSUNDEEP
sqlmysql
dimo414
dimo414

Reputation: 48804

Select all items in a table that do not appear in a foreign key of another table

Take for example an application which has users, each of which can be in exactly one group. If we want to SELECT the list of groups which have no members, what would be the correct SQL? I keep feeling like I'm just about to grasp the query, and then it disappears again.

Bonus points - given the alternative senario, where it's a many to many pairing, what is the SQL to identify unused groups?

(if you want concrete field names:) One-To-Many:

Table 'users': | user_id | group_id |
Table 'groups': | group_id |

Many-To-Many:

Table 'users': | user_id |
Table 'groups': | group_id |
Table 'user-group': | user_id | group_id |

Upvotes: 5

Views: 3095

Answers (3)

Moddasir
Moddasir

Reputation: 1449

SELECT * 
FROM groups
WHERE groups.id NOT IN (
    SELECT user.group_id 
    FROM user
)

It will return all group id which not present in user

Upvotes: 1

Rob
Rob

Reputation: 45771

Groups that have no members (for the many-many pairing):

SELECT *
FROM   groups g 
WHERE NOT EXISTS 
    (
      SELECT 1 
      FROM users_groups ug 
      WHERE g.groupid = ug.groupid
    );

This Sql will also work in your "first" example as you can substitute "users" for "users_groups" in the sub-query =)

As far as performance is concerned, I know that this query can be quite performant on Sql Server, but I'm not so sure how well MySql likes it..

Upvotes: 6

FWH
FWH

Reputation: 3223

For the first one, try this:

SELECT * FROM groups
LEFT JOIN users ON (groups.group_id=users.group_id)
WHERE users.user_id IS NULL;

For the second one, try this:

SELECT * FROM groups
LEFT JOIN user-group ON (groups.group_id=user-group.group_id)
WHERE user-group.user_id IS NULL;

Upvotes: 3

Related Questions