73

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.

Improve this question
2
  • If Ryan doesn't have Complete as the status, should the result be Highland? Commented Apr 4, 2012 at 14:07
  • 1
    SELECT [SongName] FROM [Learning] WHERE ([BandieName] LIKE '%' + '" & item.Text & "' + '%' ) AND ([BandieName] LIKE '%' + '" & item.Text & "' + '%' ) AND ([Status] LIKE 'Complete') Commented Apr 4, 2012 at 14:21

3 Answers 3

Reset to default
118

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'
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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
That is not possible, as you can see it provides the expected result here with the same data you provided.
so this query ran and provided you with the expected result, therefore it ran correctly?
Sorry, i miss understood your response. However that works very well. Thank you!
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.
|
22 
SELECT PersonName, songName, status
FROM table
WHERE name IN ('Holly', 'Ryan')

If you are using parametrized Stored procedure:

  1. Pass in comma separated string
  2. Use special function to split comma separated string into table value variable
  3. Use INNER JOIN ON t.PersonName = newTable.PersonName using a table variable which contains passed in names
Improve this answer

2 Comments

name should be an alias of the column PersonName.
@aF. You can see why you'd be confused - the sample data shows different column names to the previously mentioned definitions!
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')
Improve this answer

4 Comments

They are both in the same table?
@Sophie Yes, this is a self-join.
my table is called Learning what would the equivalent be for t1 and t2?
@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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.