Kershaw Kershaw - 4 months ago 24
SQL Question

SQL Server 2000 join with count condition

I am joining multiple tables. With one of my joins (1 to many) I want to show results only if the joined table has at least one row where a certain value exists.

This example excludes the other joins, so for simplicity's sake:

SELECT
c.Name, r.RoleID, r.RoleName
FROM
Contact c
INNER JOIN
Role r ON r.ContactID = c.ID


A Contact can have many Roles. I want to show all roles for contacts, but only get the Contacts where at least one role has RoleID = 4. I have tried a few things but no success thus far. If it matters, this is on SQL Server 2000.

Answer

Do the join twice, once where you limit to ID 4 as a filter, and again to pull in all roles:

SELECT 
    c.Name, r.RoleID, r.RoleName
FROM 
    Contact c
INNER JOIN  --if a Contact does not match any Roles with ID 4, this join will exclude that contact from the results
    Role rt /*role temp*/ ON rt.ContactID = c.ID AND rt.RoleId = 4        
INNER JOIN
    Role r ON r.ContactID = c.ID

This does make one assumption that was not explicitly stated in the question. While a Contact can have many roles, this presumes a contact will not be assigned the same role more than once.