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.

If `Ryan` doesn't have `Complete` as the status, should the result be `Highland`? — Mosty Mostacho, Apr 04 '12 at 14:07
SELECT [SongName] FROM [Learning] WHERE ([BandieName] LIKE '%' + '" & item.Text & "' + '%' ) AND ([BandieName] LIKE '%' + '" & item.Text & "' + '%' ) AND ([Status] LIKE 'Complete') — Sophie, Apr 04 '12 at 14:21

score 110 · Accepted Answer · answered Apr 04 '12 at 14:11

110

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'

answered Apr 04 '12 at 14:11

Mosty Mostacho

42,742
16
96
123

This does not bring any results up, this is similar to what i was working with before, if you used in or like 'holly' AND 'RYAN' then it brings no results up, if you use OR instead of AND it will bring up all those songs that have a status of complete for both holly and ryan, not just those that both holly and ryan both have complete for. thanks – Sophie Apr 04 '12 at 14:28
1

That is not possible, as you can see it provides the expected result [here](http://sqlfiddle.com/#!2/e1783/2) with the same data you provided. – Mosty Mostacho Apr 04 '12 at 14:31
so this query ran and provided you with the expected result, therefore it ran correctly? – Sophie Apr 04 '12 at 14:49
1

Sorry, i miss understood your response. However that works very well. Thank you! – Sophie Apr 04 '12 at 14:54
No, the logic is correct and hence, the result is correct: The logic is clear, first remove all values different from 'Ryan' and 'Holly', then group by songname. If the amount of different counted personNames is two (because Ryan and Holly are 2) that means that Ryan and Holly both have that song. Perform all the tests you require and let me know if you detect any error in the results you get. – Mosty Mostacho Apr 04 '12 at 14:55
How do we use the same IN clause for datetime column? – Eswar Feb 06 '20 at 07:07

sll · Answer 2 · 2012-04-04T14:54:50.837

19

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

edited Apr 04 '12 at 14:54

answered Apr 04 '12 at 14:04

sll

61,540
22
104
156

name should be an alias of the column PersonName. – aF. Apr 04 '12 at 14:06
@aF. You can see why you'd be confused - the sample data shows different column names to the previously mentioned definitions! – Bridge Apr 04 '12 at 14:09

score 2 · Answer 3 · answered Apr 04 '12 at 14:09

2

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')

answered Apr 04 '12 at 14:09

Sam DeHaan

10,246
2
40
48

They are both in the same table? – Sophie Apr 04 '12 at 14:23
@Sophie Yes, this is a self-join. – Sam DeHaan Apr 04 '12 at 14:29
my table is called Learning what would the equivalent be for t1 and t2? – Sophie Apr 04 '12 at 14:45
2

@Sophie they are simple aliases to separate instances of the table. They can be t1 and t2. They can be cat and dog, learning1 and learning2, l1, and l2... – Sam DeHaan Apr 04 '12 at 14:47

SQL Statement using Where clause with multiple values

3 Answers3

Linked