SQL Server 2000 join with count condition

Question

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.

I have expanded the scope of the problem into this new question: SQL Server 2000 condition on joined table

Answer 1

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.