CODEWITHSUNDEEP
mysql
Farjad Hasan
Farjad Hasan

Reputation: 3364

Fetching data in case of one -to- many relationship in MySQL

I have 3 tables users, user_group, and groups.

users have one to many relationship with groups.

If I want to fetch only those users who don't have group Mathematics.

I have using the following query for this purpose:

SELECT * FROM users 
INNER JOIN user_group ON user_group.user_id = user.UserID 
INNER JOIN groups ON user_group.group_id = groups.group_id
WHERE groups.Name <> 'Mathematics';

But it is returning multiple records against all Users. Suppose, if I have user John and he joined 3 groups Science, Mathematics and English. In this case, it will return two records of user John. I want to remove user John totally from the list.

Upvotes: 1

Views: 88

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use NOT EXISTS:

SELECT * 
FROM users AS u
WHERE NOT EXISTS (SELECT 1
                  FROM user_group AS ug 
                  INNER JOIN groups AS g ON ug.group_id = g.group_id
                  WHERE ug.user_id = u.UserID AND g.Name = 'Mathematics');

Demo here

If you want to do it using joins, then this is a way:

SELECT u.* 
FROM users AS u
LEFT JOIN (
  SELECT user_id
  FROM user_group 
  INNER JOIN groups 
    ON user_group.group_id = groups.group_id AND groups.Name = 'Mathematics'
) AS g ON u.UserID = g.user_id
WHERE g.user_id IS NULL

Demo here

Upvotes: 2

Suchak Abhay
Suchak Abhay

Reputation: 9

SELECT * FROM users 
LEFT JOIN user_group ON user.UserID = user_group.user_id
LEFT JOIN groups ON user_group.group_id = groups.group_id
WHERE groups.Name != 'Mathematics';

Upvotes: 1

Related Questions