Select for when Two People Attended Event

Forum Moderators: open

Message Too Old, No Replies

Select for when Two People Attended Event

Frank_Rizzo

10:48 am on Aug 15, 2012 (gmt 0)

I need to find records for when two specific people attended an event. I can do this via looping within php but there must be a solution via a select?

EVENT_NO, NAME
--------------
1001, A David
1001, B Elwood
1001, Z Griswold
1002, A David
1002, C Johnson
1002, D Smith
1003, A David
1003, C Johnson
1003, Z Griswold

What I want to extract are the records where A David and Z Griswold both attended the same event:

1001, A David
1001, Z Griswold
1003, A David
1003, Z Griswold

Dijkgraaf

11:35 pm on Aug 15, 2012 (gmt 0)

Something like the following ( you will have to substitute the correct table name).

SELECT EVENT_NO, NAME
FROM EVENT_TBL
WHERE EXISTS
(
SELECT 1 FROM EVENT_TBL AS EVENT_TBL2
WHERE EVENT_TBL.NO = EVENT_TBL2.NO
AND EVENT_TBL.NAME <> EVENT_TBL2.NAME
)
ORDER BY EVENT_NO, NAME