Question about a join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question about a join

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2013-11-06 : 13:48:46

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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-06 : 14:00:29

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

crugerenator
Posting Yak Master

126 Posts

Posted - 2013-11-06 : 14:28:46

Awesome, that works and applying this to my real query worked as well. Thanks again!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-06 : 14:39:02

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

sigmas
Posting Yak Master

172 Posts

Posted - 2013-11-06 : 15:28:59

[code]
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;
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources