How would I go about getting these results?

[MeSteve]

source:

ContactID ProjectID RoleID
1 1 1
1 2 2
1 3 1
2 1 1
2 2 2

How would I return the results where the noly record to show would be the
3rd one where there is no one with the RoleID of 2.

Stated differently, how do I exclude all records where there is someone with
a RoleID = 1 AND 2 and return only records that have ONLY someone with RoleID
= 1

I hope this makes sense. Thanks.

 

[KARL DEWEY]

I am missing something.
Why is row 3 returned as ContactID =1 has RoleID = 2 with ProjectID = 2.

 

[John Spencer]

Assuming that you want records where the contact id never had a roleId of 2,
you should be able to use the following. Which can be slow.

SELECT *
FROM TableName
WHERE Not EXISTS
(SELECT *
FROM TableName as Tmp
WHERE tmp.RoleID = 2 AND tmp.ContactID = TableName.ContactID)

Another method
SELECT *
FROM TableName
WHERE ContactId NOT IN (
SELECT ContactID
FROM TableName
WHERE RoleID =2)

If these are too slow and your table and field names do not require brackets
(no spaces, no "special" characters) THen

SELECT TableName.*
FROM TableName LEFT JOIN
(SELECT ContactID
FROM TableName
WHERE RoleID =2) as X
ON TableName.ContactID = X.ContactID
WHERE X.ContactID is Null




--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..