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...
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.
3 Answers
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 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.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')
David Miller brings 15 years of experience in global economics, personal finance strategy, and market dynamics. He specializes in turning complex economic trends into actionable insights for everyday readers.
