Solved

sql query matching multiple criteria

Posted on 2014-01-13
6
334 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 65

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 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Closing Comment

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 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