How would I go about getting these results?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MeSteve - 20 Feb 2008 18:39 GMT

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.

Reply to this Message

KARL DEWEY - 20 Feb 2008 19:03 GMT

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

Signature

KARL DEWEY
Build a little - Test a little

> source:
>
[quoted text clipped - 13 lines]
>
> I hope this makes sense. Thanks.

Reply to this Message

John Spencer - 20 Feb 2008 19:27 GMT

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

Signature

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

> source:
>
[quoted text clipped - 15 lines]
>
> I hope this makes sense. Thanks.

Reply to this Message