Picking the record on table2

Hello,

i have two tables named table01 and table02,

the two tables have a common column "CUSTNO" which is set as the primary key

i don't allow duplicate custno on table01, however on table02, it is possible

to have many duplicate customer number

i want to retrieve all records from table01 and get one record that exists on table02

example:

table01

cust_id cust_name
1 John Smith
2 Grace Period
3 Bill Discounted
4 Ryan Ryan

table02

cust_id disposition date
1 Busy 01/01/2007
1 Out-of-Town 01/15/2007
1 Sleeping 01/18/2007
1 Dating 01/15/2007
3 Driving 01/17/2007


the output

cust_id cust_name disposition
1 John Smith Sleeping
2 Grace Period null
3 Bill Discounted Driving
4 Ryan Ryan null

Hi,
If you are using SQL Server 2005, it is easy by using the ROW_NUMBER OVER PARTITION syntax like the script below:

select
table01.cust_id,
table01.cust_name,
t.disposition,
t.date
from table01
left join (
select
row_number() over (partition by cust_id order by cust_id) rownum,
cust_id,
disposition,
date
from table02
) t on t.cust_id = table01.cust_id and t.rownum = 1



You can use the below script for SQL2k



select
table01.cust_id,
table01.cust_name,
table02.disposition,
table02.date
from table01
left join (
select
cust_id,
max(date) date
from table02
group by cust_id
) t on t.cust_id = table01.cust_id
left join table02 on t.cust_id = table02.cust_id and t.date = table02.date



I hope it helps,

Eralper
http://www.kodyaz.com