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-29 : 14:15:10

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' union all
Select 5577, 2, '2003-01-06 00:00:00.000', 'desktop' union all
Select 5578, 2, '2003-01-06 00:00:00.000', 'Mouse'

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' union all
Select 5577, 3, 77, '2003-01-06 00:00:00.000' union all
Select 5579, 3, 78, '2003-01-06 00:00:00.000'

How can i rewrite the query to get desired output.
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
Output:
5572 test1 desk 10 2003-01-04 00:00:40.000
5573 tes4 Mousepad 40 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
5577 Null desktop 77 Null
5578 Null Mouse Null Null
5579 Null Null 78 Null

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-29 : 23:38:34

Try this...

Select a.col1, b.tcol3, c.t2col4, d.t3col3, b.tcol4 from
(Select tcol1 as col1 from @T1
union
Select t2col1 from @T2
union
Select t3col1 from @T3) as a
left join @T1 b
on a.col1 = b.tcol1 and b.tcol4 = (select max(tcol4) from @T1 x where x.tcol1 = a.col1)
left join @T2 c
on c.t2col1 = a.col1 and c.t2col3 = (select max(t2col3) from @T2 x where x.t2col1 = a.col1)
left join @T3 d
on d.t3col1 = a.col1 and d.t3col4 = (select max(t3col4) from @T3 x where x.t3col1 = a.col1)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-30 : 06:19:37

try this


select	cola	= coalesce(t1.tcol1, t2.t2col1, t3.t3col1),
	colb	= max(t1.tcol3), 
	colc	= max(t2.t2col4), 
	cold	= max(t3.t3col3),
	cole	= max(t1.tcol4)
from	(
		select	t1.tcol1, t1.tcol2, t1.tcol3, t1.tcol4
		from	@T1 t1
		where	t1.tcol4	= (select max(tcol4) from @T1 x where x.tcol1 = t1.tcol1)
	) t1
	full outer join 
	(
		select	t2.t2col1, t2.t2col2, t2.t2col3, t2.t2col4
		from	@T2 t2 
		where	t2.t2col3	= (select max(t2col3) from @T2 x where x.t2col1 = t2.t2col1)
	) t2
	on	t1.tcol1	= t2.t2col1
	full outer join 
	(
		select	t3.t3col1, t3col2, t3col3, t3col4
		from	@T3 t3
		where	t3.t3col4	= (select max(t3col4) from @T3 x where x.t3col1 = t3.t3col1)
	) t3
	on	t1.tcol1	= t3.t3col1
group by coalesce(t1.tcol1, t2.t2col1, t3.t3col1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources