sql conditional join getting wrong results

1
votes

i'm trying to join table Group on my link table userGroup, to get the groups where user with id 30 is not a member, but i can't seem to get it right.

joining with no conditions returns this

SELECT        Groups.ID, Groups.Name, UserGroup.groupID, UserGroup.userID
FROM            Groups LEFT OUTER JOIN  UserGroup ON Groups.ID = UserGroup.groupID

+----------+------------+-------------------+------------------+
| Group.ID | Group.Name | UserGroup.GroupID | userGroup.UserID |
+----------+------------+-------------------+------------------+
|        1 | g1         | 1                 | 30               |
|        2 | g2         | NULL              | NULL             |
+----------+------------+-------------------+------------------+

however when i try to sourt out the row where userID = 30, i'm getting two equally wrong results. adding the condition in the join part returns both rows but claiming the userGroup.userId is null. i'm suspecting some duplicate rows due to join, but haven't been able to figure out what exactly and how to fix it

SELECT        Groups.ID, Groups.Name, UserGroup.groupID, UserGroup.userID
FROM            Groups LEFT OUTER JOIN  UserGroup ON Groups.ID = UserGroup.groupID AND UserGroup.userID <> 30

+----------+------------+-------------------+------------------+
| Group.ID | Group.Name | UserGroup.GroupID | userGroup.UserID |
+----------+------------+-------------------+------------------+
|        1 | g1         | NULL              | NULL             |
|        2 | g2         | NULL              | NULL             |
+----------+------------+-------------------+------------------+

adding the condition to the very end of my statement, returns 0 rows.

SELECT Groups.ID, Groups.Name, UserGroup.groupID, UserGroup.userID
FROM  Groups LEFT OUTER JOIN  UserGroup ON Groups.ID = UserGroup.groupID 
WHERE UserGroup.userID <> 30

i'm running on an mssql server and using c# SqlCommand to execute the query, but i don't think that's connected to the issue

3
sqlsql-server-2008join
query seems ok try using <> – vhadalgi
UserGroup.userID != 30 this wrong Use UserGroup.userID <> 30 – senthilkumar2185
tried replacing != with <>, no difference, i'm fairly sure that writing != in c# is being translated to <>. I've run several queries with != syntax without errors – KristianMedK
also tried running the queries directly instead through c# code with the visual studio Query tool, != is automatically replaced with <> – KristianMedK
The problem isn't <> or != qsl server accept both of them! – Raphael

3 Answers

2
votes

You actually want to join trying to find where usergroup.userID = 30, not <> 30. From that join you want to return the records where there is no such usergroup record. Try something like this:

         SELECT Groups.ID, Groups.Name, UserGroup.groupID, UserGroup.userID
           FROM  Groups 
LEFT OUTER JOIN  UserGroup ON UserGroup.groupID = Groups.ID 
                          AND UserGroup.userID = 30
WHERE UserGroup.UserID IS NULL
2
votes

" to get the groups where user with id 30 is not a member"

SELECT  *
FROM  Groups g
WHERE NOT EXISTS (SELECT 1 FROM UserGroup WHERE groupID = g.iD and userID = 30);

seems more natural to me in your case.

2
votes

To get the groups where user with id 30 is not a member:

SELECT Groups.ID, Groups.Name
FROM Groups 
WHERE Groups.ID NOT IN 
(SELECT UserGroup.GroupID FROM UserGroup WHERE UserGroup.UserID = 30)