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.
This question is related to
sql
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'
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')
SELECT PersonName, songName, status
FROM table
WHERE name IN ('Holly', 'Ryan')
If you are using parametrized Stored procedure:
INNER JOIN ON t.PersonName = newTable.PersonName using a table variable which contains passed in namesSource: Stackoverflow.com