SQL Statement using Where clause with multiple values
SqlSql Problem Overview
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.
Sql Solutions
Solution 1 - 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'
Solution 2 - Sql
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
- Use
INNER JOIN ON t.PersonName = newTable.PersonName
using a table variable which contains passed in names
Solution 3 - Sql
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')