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:
c.Name, r.RoleID, r.RoleName
Role r ON r.ContactID = c.ID
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.