CODEWITHSUNDEEP
sqlsql-serverduplicates
jhammer
jhammer

Reputation: 3

SQL - Find duplicate children

I have a table containing meetings:

MeetID      Description
-----------------------------------------------------
1           SQL Workshop
2           Cake Workshop

I have another table containing all participants in the meetings:

PartID      MeetID      Name          Role
-----------------------------------------------------
1           1           Jan           Coordinator
2           1           Peter         Participant
3           1           Eva           Participant
4           1           Michael       Coordinator
5           2           Jan           Coordinator
6           2           Peter         Participant

I want to find is a list of all meetings that have 2 or more participants with Role = 'Coordinator'. Eg. in the example above that would be the meeting with MeetID=1 and not 2.

I cannot for the life of me figure out how to do this, allthough I think it should be simple :-) (I am using SQL Server 2012)

Upvotes: 0

Views: 123

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

This is easy to do using group by and having:

select MeetId
from participants p
where Role = 'Coordinator'
group by MeetId
having count(*) >= 2;

Note: Role is a potential keyword/reserved word, so it is a bad choice for a column name.

Upvotes: 5

Related Questions