query advices needed. :) - 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 2000 Forums

Transact-SQL (2000)

query advices needed. :)

Author

Topic

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-09-02 : 08:46:19

[code]

tbl_matches
[ID] [Name] [Paired_To]
1 Name1 2
2 Name2 1
3 Name3 4
4 Name4 3

i tried doing this query

select m.[name] , m1.[Name] as Name1 from tbl_matches m
inner join tbl_matches m1 on m1.paired_to = m.id

it would display like this...
[Name] [Name1]
-----------------------------------
Name1 Name2
Name2 Name1
Name3 Name4
Name4 Name3

the desired result should be like this...
[Name] [Name1]
-----------------------------------
Name1 Name2
Name3 Name4

what would be the correct query? please help....

[/code]

TCC

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-09-02 : 09:30:40

[code]
declare @tblmatches table([id] int, [name] varchar(50), [paired_to] int)

insert into @tblmatches
select 1,'Name1',2
union all select 2, 'Name2', 1
union all select 3, 'Name3', 4
union all select 4, 'Name4', 3

select m.[name] , m1.[Name] as Name1 from @tblmatches m
inner join @tblmatches m1 on m1.paired_to = m.id
[/code]

TCC

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-02 : 09:33:23

Something like this...

declare @tbl table
(
	id int,
	name varchar(20),
	paired_to int
)

insert into @tbl
select 1, 'Name1', 2 union all
select 2, 'Name2', 1 union all
select 3, 'Name3', 4 union all
select 4, 'Name4', 3

select * from (select m.[id], m.[name] , m1.[id] as [id1], m1.[Name] as Name1  from @tbl m 
	inner join @tbl m1 on m1.paired_to = m.id) as test where test.id < test.id1

Harsh Athalye
India.
"Nothing is Impossible"

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-09-02 : 09:33:58

thanks a lot dude!!!!

TCC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources