SQL Statement using Where clause with multiple values

Question

I have a table that has multiple rows with the following fields:

PersonName SongName Status 

I want to use names selected from a multiple selection listbox, which I can retrieve the values, and then do a where clause so it shows the song names that the selected people can all play, therefore status is complete.

For example:

 PersonName      SongName    Status   Holly           Highland    Complete  Holly           Mech        Complete   Ryan            Highland    Complete 

If I select Holly and Ryan from the list box and press the button the query should just show Highland as that is what they both know.

Answer 1

Try this:

select songName from t where personName in ('Ryan', 'Holly') group by songName having count(distinct personName) = 2 

The number in the having should match the amount of people. If you also need the Status to be Complete use this where clause instead of the previous one:

where personName in ('Ryan', 'Holly') and status = 'Complete' 

Answer 2

SELECT PersonName, songName, status FROM table WHERE name IN ('Holly', 'Ryan') 

If you are using parametrized Stored procedure:

1. Pass in comma separated string
2. Use special function to split comma separated string into table value variable
3. Use INNER JOIN ON t.PersonName = newTable.PersonName using a table variable which contains passed in names