tsql - SQL Server 2000 join with count condition -

i joining multiple tables. 1 of joins (1 many) want show results if joined table has @ least 1 row value exists.

this example excludes other joins, simplicity's sake:

select      c.name, r.roleid, r.rolename      contact c inner join     role r on r.contactid = c.id 

a contact can have many roles. want show roles contacts, contacts @ least 1 role has roleid = 4. have tried few things no success far. if matters, on sql server 2000.

i have expanded scope of problem new question: sql server 2000 condition on joined table

do join twice, once limit id 4 filter, , again pull in roles:

select      c.name, r.roleid, r.rolename      contact c inner join  --if contact not match roles id 4, join exclude contact results     role rt /*role temp*/ on rt.contactid = c.id , rt.roleid = 4         inner join     role r on r.contactid = c.id 

this make 1 assumption not explicitly stated in question. while contact can have many roles, presumes contact not assigned same role more once.