SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 sqlquery help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqldba2k6
Posting Yak Master

176 Posts

Posted - 05/13/2007 :  17:19:35  Show Profile  Reply with Quote
Please help me in writing the query......

table1: tcol1,tcol2 composite pkeys.
table2:t2col1,t2col2 composite pkeys.
Table3:t3col1,t3col2 composite pkeys.

Conditions:eliminating the duplicates by taking the max date

Table1
------

tcol1 tcol2 tcol3 tcol4
----- ----- ----- -----
5572 1 test1 2003-01-04 00:00:00.000
5572 1 test1 2003-01-05 00:00:00.000
5573 1 tes4 2003-01-05 00:00:00.000
5574 1 tes5 2003-01-06 00:00:00.000

Table2
------
t2col1 t2col2 t2col3 t2col4
----- ----- ----- ------
5572 2 2003-01-04 00:00:00.000 Mouse
5572 2 2003-01-05 00:00:00.000 desk
5573 2 2003-01-05 00:00:00.000 Mousepad
5574 2 2003-01-06 00:00:00.000 Hw

Table3
------
t3col1 t3col2 t3col3 t3col4
----- ----- ----- ------
5572 3 09 2003-01-04 00:00:00.000
5572 3 10 2003-01-05 00:00:00.000
5573 3 40 2003-01-05 00:00:00.000
5574 3 65 2003-01-06 00:00:00.000


I want the output:

5572 test1 desk 10 2003-01-05 00:00:00.000
5573 tes4 Mousepad 40 2003-01-05 00:00:00.000
5574 tes5 Hw 65 2003-01-06 00:00:00.000

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 05/14/2007 :  00:32:36  Show Profile  Reply with Quote
Select T1.tcol1, T1.tcol3, T2.t2col4, T3.t3col3, T1.tcol4
From @T1 T1
Inner join @T2 T2 on T2.t2col1 = T1.tcol1
and T1.tcol4 = T2.t2col3
Inner join @T3 T3 on T3.t3col1 = T1.tcol1
and T1.tcol4 = T3.t3col4
Where T3.t3col4 = ( Select Max(t3col4) From @T3 Where t3col1 = T1.tcol1)
Go to Top of Page

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 05/14/2007 :  00:33:54  Show Profile  Reply with Quote
Sorry...
some part of solution is missed

-- 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-05 00:00:00.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'

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-05 00:00:00.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'




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-05 00:00:00.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'


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


Regard's
Peter
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 05/14/2007 :  00:43:21  Show Profile  Reply with Quote
Alternative solution

select	tcol1, tcol3, t2col4, tcol4
from	@T1 inner join @T2
	on	tcol1	= t2col1
	and	tcol4	= t2col3
	inner join 
	(
		select	t3col1, t3col4 = max(t3col4)
		from	@T3
		group by t3col1
	) m
	on	tcol1	= t3col1
	and	tcol4	= t3col4
order by tcol1



KH

Go to Top of Page

shallu1_gupta
Constraint Violating Yak Guru

India
394 Posts

Posted - 05/14/2007 :  00:51:46  Show Profile  Reply with Quote
try this..
Select a.tcol1, a.tcol2 , t2col4, t3col3 , t3col4
from table2
inner join
(Select tcol1, tcol2, tcol3, max(tcol4)
from Table1
group by tcol1, tcol2, tcol3) a
on t2col1 = a.tcol1 and t2col3 = a.tcol4
inner join Table3 on
t3col1 = a.tcol1 and t3col4 = a.tcol4

Go to Top of Page

sqldba2k6
Posting Yak Master

176 Posts

Posted - 05/15/2007 :  10:51:43  Show Profile  Reply with Quote
Thanks !!!!!!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000