Find results based on other results In SQL Server 2008, I have a table called Info.Case, which lists all the cases we have, Info.Person which lists all the people in the database, and a table called Links.Case_Person, which lists how each person relates to each case.

Here is what I am trying to do:

Find all the cases John Smith (Person_ID = 123) is listed as Primary (Role_ID = 5) or Secondary (Role_ID = 11).

So far this is not an issue:

SELECT * FROM Info.Case IC
INNER JOIN Links.Case_Person LC
ON IC.Case_ID = LC.Case_ID
INNER JOIN Info.Person IP
ON LC.Person_ID = IP.Person_ID
WHERE LC.Person_ID = 123 AND LC.Role_ID IN (5, 11)


This generates 100 Cases.

Now I want know know if there was also an assistant (Role_ID = 22) on any of these specific 100 John Smith cases. So I want to search just the 100 cases for any Role_ID = 22.

How do I do this?
Without sample data and desired results, can't be too confident, but it sounds like you just need to add a check for the existence of assistants on the same cases:

AND EXISTS (SELECT 1 FROM Links.Case_Person AS cp
  WHERE cp.Case_ID = LC.Case_ID AND cp.Role_ID = 22);


Based on the comments below it seems your description doesn't quite match your stated requirements above. Perhaps, then:

SELECT *, 
  HasAssistant = CASE WHEN LC2.Case_ID IS NOT NULL THEN 1 ELSE 0 END
FROM Info.Case IC
INNER JOIN Links.Case_Person LC
ON IC.Case_ID = LC.Case_ID
INNER JOIN Info.Person IP
ON LC.Person_ID = IP.Person_ID
LEFT OUTER JOIN Links.Case_Person AS LC2
ON LC.Case_ID = LC2.Case_ID
AND LC2.Role_ID = 22
WHERE LC.Person_ID = 123 AND LC.Role_ID IN (5, 11);


(Note that if it is possible for a case to have more than one assistant, that case will produce multiple rows here. There are ways around that, but your specific requirements are still not clear to me.)