SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Question about a join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

crugerenator
Posting Yak Master

126 Posts

Posted - 11/06/2013 :  13:48:46  Show Profile  Send crugerenator an AOL message  Reply with Quote
I'm trying to figure out a join and was hoping to get an example. Here's a simplified version of my problem.

Here's a table that keeps score of matches for a game, and I want to find all users that have played matches against both player 'user1' and 'user2'.

The table looks like:

challenger   defender   score   
----------   --------   -----  
user1          ron       10     
user2          ron       50
giraffee       user1     20
cat            ron       30
rick           user1     10
rick           user2     20  


So, my results should show users 'ron' and 'rick' since they've both played a game against both user1 and user2.

I'm trying a bunch of ways to get these results (joining the table to itself, views, unions) and nothing seems to be working. Any ideas?

TG
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 11/06/2013 :  14:00:29  Show Profile  Reply with Quote
Here's one way:

select defender as player 
from yourTable 
where challenger in ('user1', 'user2') 
group by defender 
having count(distinct challenger) = 2

union

select challenger as player 
from yourTable 
where defender in ('user1', 'user2') 
group by challenger 
having count(distinct defender) = 2



Be One with the Optimizer
TG
Go to Top of Page

crugerenator
Posting Yak Master

126 Posts

Posted - 11/06/2013 :  14:28:46  Show Profile  Send crugerenator an AOL message  Reply with Quote
Awesome, that works and applying this to my real query worked as well. Thanks again!
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 11/06/2013 :  14:39:02  Show Profile  Reply with Quote
Great - glad it worked. In case you weren't following along the "having count(distinct ...) = 2" has to match the number of users you are checking for. So for (user1, user2, user3) the count(distinct) needs to = 3.

Be One with the Optimizer
TG
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 11/06/2013 :  15:28:59  Show Profile  Reply with Quote

declare @sample table 
(challenger varchar(10),
defender varchar(10),
score tinyint,
check(challenger <> defender));

insert into @sample(challenger, defender, score) values 
('user1','ron',10),
('user2','ron',50),
('giraffee','user1',20),
('cat','ron',30),
('rick','user1',10),
('rick','user2',20),
('cat', 'user1',0),
('user1', 'cat',0);

--TG suggestion
select defender as player 
from @sample
where challenger in ('user1', 'user2') 
group by defender 
having count(distinct challenger) = 2

union

select challenger as player 
from @sample
where defender in ('user1', 'user2') 
group by challenger 
having count(distinct defender) = 2

--sigma suggestion 
select a as player
from
(
select defender a, challenger b from @sample
union
select challenger, defender from @sample
)d
where b in ('user1', 'user2')
group by a
having count(distinct b) = 2;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000