Need help to built SQL statement

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)

Need help to built SQL statement

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-04-19 : 15:28:30

I've tables and rows as follow,

declare @t1 table
(idx int identity(1,1), TickN varchar(20),
TID varchar(20), dFrom varchar(10), dTo varchar(10), SeatN varchar(10));
/*@t1(TickN) is unique*/
insert into @t1 values('SG1234','_d100200009082','SG','KL','3A');
insert into @t1 values('SG1236','_d100200009082','SG','KL','3C');
insert into @t1 values('SG1239','_d100200009082','SG','KL','3A');
insert into @t1 values('SG1236','_d100200009082','SG','KL','3B');
select * from @t1

declare @t2 table
(idx int identity(1,1), TID varchar(20), SeatN varchar(10), Posi varchar(10));
/*@t2(TID,SeatN) is unique*/
insert into @t2 values('_d100200009082','3A','110');
insert into @t2 values('_d100200009082','3C','100');
insert into @t2 values('_d100200009082','3B','100');
select * from @t2
/*
Relationship @t1 to @t2 is Many to 1
@t1(TID,SeatN) is refer to @t2(TID,SeatN)
 */

How SQL look's like to display resultset as follow,

Output
TID           | dFrom | dTo  | SeatN  | Posi
------------------------------------------------
_d100200009082	SG 	    KL	   3A       110
_d100200009082	SG	    KL	   3C       100
_d100200009082	SG	    KL	   3B       100

You'll see, duplicate rows in @t1(TID,SeatN) is removed

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-19 : 15:36:56

[code]
select distinct a.TID, b.dFrom, b.dTo, a.seatn, a.Posi
from @t2 a join @t1 b on a.tid = b.tid and a.seatN = b.seatn
order by a.seatn[/code]

quote:
Originally posted by Delinda

I've tables and rows as follow,
[code]declare @t1 table
(idx int identity(1,1), TickN varchar(20),
TID varchar(20), dFrom varchar(10), dTo varchar(10), SeatN varchar(10));
/*@t1(TickN) is unique*/
insert into @t1 values('SG1234','_d100200009082','SG','KL','3A');
insert into @t1 values('SG1236','_d100200009082','SG','KL','3C');
insert into @t1 values('SG1239','_d100200009082','SG','KL','3A');
insert into @t1 values('SG1236','_d100200009082','SG','KL','3B');
select * from @t1

declare @t2 table
(idx int identity(1,1), TID varchar(20), SeatN varchar(10), Posi varchar(10));
/*@t2(TID,SeatN) is unique*/
insert into @t2 values('_d100200009082','3A','110');
insert into @t2 values('_d100200009082','3C','100');
insert into @t2 values('_d100200009082','3B','100');
select * from @t2
/*
Relationship @t1 to @t2 is Many to 1
@t1(TID,SeatN) is refer to @t2(TID,SeatN)
*/[/code]

How SQL look's like to display resultset as follow,
[code]Output
TID | dFrom | dTo | SeatN | Posi
------------------------------------------------
_d100200009082 SG KL 3A 110
_d100200009082 SG KL 3C 100
_d100200009082 SG KL 3B 100[/code]

You'll see, duplicate rows in @t1(TID,SeatN) is removed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 01:10:21

[code]
select a.TID, b.dFrom, b.dTo, a.seatn, a.Posi
from @t2 a
join (select min(TickN) AS First,TID
from @t2
group by TID)a1
on a1.TID = a.TID
and a1.First = a.TickN
join @t1 b
on a.tid = b.tid
and a.seatN = b.seatn
order by a.seatn
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-04-20 : 21:51:44

tq to both of you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 01:40:34

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources