Intersection operation with the same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Intersection operation with the same table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-23 : 07:35:20

Sreedhar writes "Hi,

This is the scenario:
In a school annual competitions
There are Events (say 10 events this may increase also).
There are people registered for the events.(each participant will have a unique id)

I would like to get list of those participants who have registered for all the events .

prefer without using a cursor"

eralper
Yak Posting Veteran

66 Posts

Posted - 2005-08-23 : 08:42:11

Hi, you can check the below sql script to get the list of participants attending to all events

select * from participants where participantid not in (
select distinct c.participantid
from (
select e.eventid, p.participantid
from events e, participants p
) c
left join EventParticipants ep on ep.eventid = c.eventid and ep.participantid = c.participantid
where ep.participantid is null
)

Eralper
http://www.kodyaz.com

-------------
Eralper
http://www.kodyaz.com

eralper
Yak Posting Veteran

66 Posts

Posted - 2005-08-23 : 08:51:35

Or you may use to check the attented events count

SELECT * FROM Participants WHERE ParticipantId IN (
SELECT ParticipantId
FROM EventParticipants
GROUP BY ParticipantId
HAVING COUNT(Distinct EventId) = (SELECT COUNT(1) FROM Events)
)

-------------
Eralper
http://www.kodyaz.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources