SQL Statement using Where clause with multiple values
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.
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.
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'
Read more... Read less...
SELECT PersonName, songName, status FROM table WHERE name IN ('Holly', 'Ryan')
If you are using parametrized Stored procedure:
- Pass in comma separated string
- Use special function to split comma separated string into table value variable
INNER JOIN ON t.PersonName = newTable.PersonNameusing a table variable which contains passed in names
Select t1.SongName From tablename t1 left join tablename t2 on t1.SongName = t2.SongName and t1.PersonName <> t2.PersonName and t1.Status = 'Complete' -- my assumption that this is necessary and t2.Status = 'Complete' -- my assumption that this is necessary and t1.PersonName IN ('Holly', 'Ryan') and t2.PersonName IN ('Holly', 'Ryan')