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

User · Accepted Answer

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

User · Answer

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

User · Answer

Select t1 SongName From tablename t1 left join tablename t2  on t1 SongName   t2 SongName     and t1 PersonName  lt  gt  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

[sql] SQL Statement using Where clause with multiple values

Examples related to sql