Solved

sql query matching multiple criteria

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 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