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)
 sqlquery help

Author  Topic 

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-05-13 : 17:19:35
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 - 2007-05-14 : 00:32:36
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 - 2007-05-14 : 00:33:54
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)

17689 Posts

Posted - 2007-05-14 : 00:43:21
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

394 Posts

Posted - 2007-05-14 : 00:51:46
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 - 2007-05-15 : 10:51:43
Thanks !!!!!!!
Go to Top of Page
   

- Advertisement -