?
Solved

sql query matching multiple criteria

Posted on 2014-01-13
6
Medium Priority
?
341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

777 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