query help - 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

SQL Server Development (2000)

query help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-05-26 : 19:28:45

Please help me in getting the output.

-- Prepare sample data
Declare @T1 table (tcol1 Int, tcol2 Int, tcol3 varchar(100), tcol4 datetime)

Insert into @T1
Select 5572, 1, 'test1', '2003-01-04 00:00:00.000' Union all
Select 5572, 1, 'test1', '2003-01-04 00:00:40.000' Union all
Select 5573, 1, 'tes4', '2003-01-05 00:00:00.000' Union all
Select 5574, 1, 'tes5', '2003-01-06 00:00:00.000' Union all
Select 5575, 1, 'tes6', '2003-01-06 00:00:00.000' Union all
Select 5576, 1, 'tes7', '2003-01-06 00:00:00.000'

Declare @T2 table (t2col1 Int, t2col2 Int, t2col3 Datetime, t2col4 varchar(100))
Insert into @T2
Select 5572, 2, '2003-01-04 00:00:00.000', 'Mouse' Union all
Select 5572, 2, '2003-01-04 00:00:58.000', 'desk' Union all
Select 5573, 2, '2003-01-05 00:00:00.000', 'Mousepad' Union all
Select 5574, 2, '2003-01-06 00:00:00.000', 'Hw' union all
Select 5575, 2, '2003-01-06 00:00:00.000', 'Laptop'

Declare @T3 Table (t3col1 Int, t3col2 Int, t3col3 Int, t3col4 Datetime )
Insert into @T3
Select 5572, 3, 09, '2003-01-04 00:00:00.000' Union all
Select 5572, 3, 10, '2003-01-04 00:00:32.000' Union all
Select 5573, 3, 40, '2003-01-05 00:00:00.000' Union all
Select 5574, 3, 65, '2003-01-06 00:00:00.000' union all
Select 5576, 3, 76, '2003-01-06 00:00:00.000'

Select T1.tcol1, T1.tcol3, T2.t2col4, T3.t3col3, T1.tcol4
From @T1 T1
Inner join @T2 T2 on T2.t2col1 = T1.tcol1
Inner join @T3 T3 on T3.t3col1 = T1.tcol1
Where
T3.t3col4 = ( Select Max(t3col4) From @T3 Where t3col1 = T1.tcol1)
and T2.t2col3 = ( Select Max(t2col3) From @T2 Where t2col1 = T1.tcol1)
and T1.tcol4 = ( Select Max(tcol4) From @T1 Where tcol1 = T1.tcol1)

Output:
5572 test1 desk 10 2003-01-04 00:00:40.000
5573 tes4 Mousepad40 2003-01-05 00:00:00.000
5574 tes5 Hw 65 2003-01-06 00:00:00.000
5575 tes6 laptop null 2003-01-06 00:00:00.000
5576 tes7 null 76 2003-01-06 00:00:00.000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-27 : 01:48:21

[code]
select t1.tcol1, t1.tcol3, t2.t2col4, t3.t3col3, t1.tcol4
from @T1 t1 left join @T2 t2
on t1.tcol1 = t2.t2col1
and t2.t2col3 = (select max(t2col3) from @T2 x where x.t2col1 = t1.tcol1)
left join @T3 t3
on t1.tcol1 = t3.t3col1
and t3.t3col4 = (select max(t3col4) from @T3 x where x.t3col1 = t1.tcol1)
where t1.tcol4 = (select max(tcol4) from @T1 x where x.tcol1 = t1.tcol1)
order by t1.tcol1
[/code]

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-05-27 : 10:20:37

Thanks KH !!!
With the above 3 tables please suggest the best DB relationship..
Primary key/Foreign..

I thought of creating the
Table T1: tcol,tcol2,tcol3 composite primary key.
Table T2: t2col1,t2col2,t2col3 composite primary key.
Table T3: t3col1,t3col2,t3col4 composite primary key.

Thanks for your help in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources