Get most recent data from joined tables?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Get most recent data from joined tables??

Author

Topic

ElectricC
Starting Member

1 Post

Posted - 2008-04-25 : 12:19:58

Hello, i have this problem
Table1
tbl1

pcb varchar(30)
serial varchar(30)
result varchar(30)

tbl2:

pcb varchar(30)
date_time varchar(30)
result varchar(30)
data1 varchar(30)
data2 varchar(30),

what i need is query the tbl1 for a range of serials,get the pcb and for those pcb's query the tbl2 for data1,data2
The resultSet should be a join on the two tables, Columns {serial} from tbl1 and {pcb,date_time,data1,data2} from tbl2

Please follow my simple example:
Suppose tbl1 has these 2 records
tbl1 = pcb1,sn1,pass
pcb2,sn2,pass
pcb3,sn3,pass

tbl2= pcb1,date1,pass,dataX1,dataY1
pcb1,date2,pass,dataX2,dataY2
pcb2,date3,pass,dataX3,dataY3
pcb3,date4,pass,dataX4,dataY4
pcb1,date5,pass,dataX5,dataY5
pcb2,date6,pass,dataX6,dataY6

where date1 is the most recent date and date6 the least recent

Request:what i want is for serial>=sn1 and serial<=sn2,get the pcbs from tbl1(which are pcb1 and pcb2) and based on these, query the tbl2
for the other data but retrieve only most recent records.

The correct ResultSet should be

pcb1,sn1,date1,dataX1,dataY1
pcb2,sn2,date3,dataX3,dataY3

and not
pcb1,date1,pass,dataX1,dataY1
pcb1,date2,pass,dataX2,dataY2
pcb1,date5,pass,dataX5,dataY5
pcb2,date3,pass,dataX3,dataY3
pcb2,date6,pass,dataX6,dataY6

What i already did is this:


select max(CONVERT(DATETIME,tbl2.date_time,103)),tbl1.serial,tbl2.pcb
  from tbl2 
 left JOIN tbl1
  ON tbl2.Pcb=tbl1.pcb
  where tbl1.serial>='1' and tbl1.serial<='53'
  and tbl2."Result" like 'pass' and tbl1."result" like 'pass'
  group by tbl2.pcb,tbl1.serial;

This works correctly for getting serial from tbl1, date_time and pcb from tbl2.But unfortunately i also need data1 and data2 columns from tbl2.
If i include them in the Select Clause i have to include them also in the group by ,and this gives me also duplicate records (by using this OR philosophy).I mean, it would give all records containing (pcb1,pcb2),much like my example

How can i resolve this issue?
Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-25 : 12:29:03

Thanks for detailed explanation.

You can do like this

SELECT t1.pcb,t1.serial,t2.date_time,t2.data1,t2.data2
FROM tbl1 t1
INNER JOIN tbl2 t2
ON t1.pcb=t2.pcb
INNER JOIN (SELECT pcb,MAX(date_time) AS maxdate
            FROM tbl2 
            GROUP BY pcb) tmp
ON tmp.pcb=t2.pcb
AND tmp.maxdate=t2.date_time
WHERE t1.serial >= @serialstart
AND t1.serial <= @serialend

or use ROW_NUMBER function if you are using sql 2005 with compatibilty level 90

SELECT t.pcb,t.serial,t.date_time,t.data1,t.data2
FROM
(
SELECT 
ROW_NUMBER() OVER(PARTITION BY t1.pcb ORDER BY t2.date_time DESC) AS RowNo,
t1.pcb,t1.serial,t2.date_time,t2.data1,t2.data2
FROM tbl1 t1
INNER JOIN tbl2 t2
ON t1.pcb=t2.pcb
WHERE t1.serial >= @serialstart
AND t1.serial <= @serialend
)t
WHERE t.RowNo=1

EDIT: missed where clause

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources