Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

sql query matching multiple criteria

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
johnnyg123
Asked:
johnnyg123
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
John_VidmarCommented:
select	playerid
from	bonus
where	bonusid in (2,3)
group
by	playerid
having	count(distinct bonusid) = 2

Open in new window

0
 
Easwaran ParamasivamCommented:
select playerid from YourTable where bonusid = 2 
intersect
select playerid from YourTable where bonusid = 3

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
johnnyg123Author Commented:
Perfect....Simple as can be
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Easwaran ParamasivamCommented:
Have you tried other two solutions? Whether they are failing? If they are working you would have split the points. Isn't it?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now