help with farily simple query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

help with farily simple query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-02-16 : 10:17:39

Hi,

I have a table as follows, and what I want to do is SELECT "double matches". For instance in the sample data below 100,500 would be a double match. (both users have selected other users as a match)

I thought this was simple, but I'm stuck on how to proceed from here. Any help is much appreciated...

Thanks again!:)
mike123

CREATE PROCEDURE dbo.[select_DoubleMatches]
(
@userID [int]
)

SELECT * FROM [tblMatches] WHERE userID = @userID AND ...

tblMatches
userID,matchID
100,200
100,300
100,500
500,100

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 10:22:08

[code]declare @tblmatches table (userid smallint, matchid smallint)

insert @tblmatches
select 100, 200 union all
select 100, 300 union all
select 100, 500 union all
select 500, 100

select a as player1,
b as player2
from (
select userid as a, matchid as b from tblmatches union all
select matchid, userid from tblmatches
) as x
group by a,
b
having count(*) > 1[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 10:24:28

[code]declare @tblmatches table (userid smallint, matchid smallint)

insert @tblmatches
select 100, 200 union all
select 100, 300 union all
select 100, 500 union all
select 500, 100

select t1.*
from @tblmatches as t1
inner join @tblmatches as t2 on t2.userid = t1.matchid and t2.matchid = t1.userid[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 10:30:42

[code]declare @tblmatches table (userid smallint, matchid smallint)

insert @tblmatches
select 100, 200 union all
select 100, 300 union all
select 100, 500 union all
select 500, 100

select distinct case when t1.userid < t1.matchid then t1.userid else t1.matchid end as userid,
case when t1.userid > t1.matchid then t1.userid else t1.matchid end as matchid
from @tblmatches as t1
inner join @tblmatches as t2 on t2.userid = t1.matchid and t2.matchid = t1.userid
where t1.userid = @userid[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 10:37:16

[code]declare @tblmatches table (userid smallint, matchid smallint)

insert @tblmatches
select 100, 200 union all
select 100, 300 union all
select 100, 500 union all
select 500, 100

declare @userid int
select @userid = 500

select distinct case when t1.userid = @userid then t1.userid else t1.matchid end as userid,
case when t1.userid = @userid then t1.matchid else t1.userid end as matchid
from @tblmatches as t1
inner join @tblmatches as t2 on t2.userid = t1.matchid and t2.matchid = t1.userid[/code]

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-02-16 : 10:48:07

Hi Peter,

Thanks once again for all your help! I used the third example, however I removed a little bit. I wasn't sure exactly the need for the case structure, and with the data I had in my DB it was transposing the userID and matchID. I don't think for my application I can see any reason why it matters if the userID is bigger than the matchID.

I'm left with this and it seems to be working perfectly.

Thanks again! and have a great weekend :)

mike123

create PROCEDURE dbo.[select_DoubleMatches]
(
@userID [int]
)

AS SET NOCOUNT ON

select distinct

t1.userID as userID,
t1.matchUserID as matchUserID

from tblMeetMe_Matches as t1
inner join tblMeetMe_Matches as t2 on t2.userID = t1.matchUserID and t2.matchUserID = t1.userID
where t1.userID = @userid

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources