?
Solved

sql query matching multiple criteria

Posted on 2014-01-13
6
Medium Priority
?
352 Views
Last Modified: 2014-01-13
here is some sample data from player bonus table

playerid  bonusid
1234        2
1234         3
1235        2


Trying to write a query that returns list of player ids that have an entry with bonusid of 2 AND a bonusid of 3

Using the sample above only player id of 1234 would be returned
0
Comment
Question by:johnnyg123
6 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39777635
Guessing there's a more graceful way to do that, but the below T-SQL works.  
Copy-paste it into your SSMS, execute to verify it meets your needs, then modify to work with your tables.
CREATE TABLE bonus (playerid int, bonusid int)
GO

INSERT INTO bonus (playerid, bonusid) 
VALUES (1234, 2), (1234, 3), (1235,2)

SELECT p2.playerid
FROM (SELECT DISTINCT playerid FROM bonus WHERE bonusid=2) p2
	JOIN (SELECT DISTINCT playerid FROM bonus WHERE bonusid=3) p3 ON p2.playerid = p3.playerid
ORDER BY p2.playerid

Open in new window

0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 2000 total points
ID: 39777658
select	playerid
from	bonus
where	bonusid in (2,3)
group
by	playerid
having	count(distinct bonusid) = 2

Open in new window

0
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 39777751
select playerid from YourTable where bonusid = 2 
intersect
select playerid from YourTable where bonusid = 3

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 

Author Closing Comment

by:johnnyg123
ID: 39777794
Perfect....Simple as can be
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39777809
Looks like there are three correct answers here, one of which came first, and a quick glance shows that the simplest one came last.

Can you comment on them?
0
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 39777819
Have you tried other two solutions? Whether they are failing? If they are working you would have split the points. Isn't it?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question