SQL Server Performance Forum – Threads Archive

Join Question

I have a basic qustion that for some reason I can’t figure out.
Below I have 2 tables, Table UserGroup’s ID column is a FK from Table Users. I’d like
to get a result set back from Table A of users not assigned to a particular group ID. So for this query I’d expect to get back:
3 Bill
4 Steve SELECT Distinct a.ID, a.Desc
FROM Users AS a INNER JOIN UserGroups AS b ON a.ID != b.ID
WHERE (b.GroupID = 1) Table Users Table User Groups
———– ——————
ID DESC GroupID ID
1 John 1 1
2 Sue 1 2
3 Bill 2 1
4 Steve Any idea what I’m doing wrong here? Thanks,
Steve

---

Your query will get all rows out of the users table that do not have a group id of 1 to make your query work you can either select id, desc
from Users as u
where id not in ( select id from UserGroups where GroupId = 1 ) or select u.id, u.desc
from Users as u
left outer join UserGroups as ug
on u.ID = ug.id
where ug.GroupId = 1
group by u.id, u.desc
having count( ug.id ) = 0 or select id, desc
from Users as u
where not exists (select id from UserGroups where GroupId = 1 and id = u.id) Cheers
Twan

---

]]>