Help on Join

HI,

I have two tables

tbl_one
-
Call_NO(PK) | Created_dt
0001 | 6/01/2007
0002 | 6/01/2007
0003 | 6/02/2007
0004 | 6/02/2007
0005 | 6/03/2007

tbl_two
-

ID | Call_NO(FK) | Code | Updated_dt (mm/dd/yyyy)
1 | 0001 | SR | 6/07/2007
2 | 0001 | 16 | 6/08/2007
3 | 0001 | 15 | 6/09/2007
4 | 0002 | 14 | 6/10/2007
5 | 0002 | 13 | 6/11/2007

Using this tables i need to write a query to output follwing

OUTPUT :

Call_No | Created_dt | Code | Updated_dt
0001 | 6/01/2007 | 15 | 6/09/2007
0002 | 6/01/2007 | 13 | 6/11/2007
0003 | 6/02/2007 | |
0004 | 6/02/2007 | |
0005 | 6/03/2007 | |

Conditions are for each call we need to take "code" using last updated_dt. For ex: In tbl_two we have three records for call no 001 in this we need to take last updated date ie, 6/09/2007 and join with tbl_one on call_no and show records as shown in output.

Please help me to write this query,

Here it is,

--Code Snippet

Create Table #tbl_one (

[Call_NO] int ,

[Created_dt] datetime

);

Insert Into #tbl_one Values('0001','6/01/2007');

Insert Into #tbl_one Values('0002','6/01/2007');

Insert Into #tbl_one Values('0003','6/02/2007');

Insert Into #tbl_one Values('0004','6/02/2007');

Insert Into #tbl_one Values('0005','6/03/2007');

Create Table #tbl_two (

[ID] int ,

[Call_NO] Varchar(100) ,

[Code] Varchar(100) ,

[Updated_dt] datetime

);

Insert Into #tbl_two Values('1','0001','SR','6/07/2007');

Insert Into #tbl_two Values('2','0001','16','6/08/2007');

Insert Into #tbl_two Values('3','0001','15','6/09/2007');

Insert Into #tbl_two Values('4','0002','14','6/10/2007');

Insert Into #tbl_two Values('5','0002','13','6/11/2007');

Code Snippet

Select

A.[Call_NO]

, A.[Created_dt]

, C.[Code]

, B.[Updated_dt]

from

#tbl_one A

Left Outer Join

(

Select

[Call_NO]

,Max([Updated_dt]) [Updated_dt]

from

#tbl_Two

group by

[Call_NO]

) as B

On A.[Call_NO] = B.[Call_NO]

Left Outer Join

#tbl_Two C

on B.[Updated_dt] = C.[Updated_dt]

and B.[Call_NO] = C.[Call_NO]

|||SELECT t1.Call_No, t1.Created_dt, ISNULL(t2.Code, '') as Code, ISNULL(t2.Updated_dt, '') as Updated_dt
FROM tbl_one t1
LEFT JOIN (SELECT ttwo.Call_NO, ttwo.Code, ttwo.Updated_dt

FROM tbl_two ttwo

(SELECT Call_NO, MAX(Updated_dt) LastUpdate FROM tbl_two GROUP BY Call_NO) lstUpd
ON ttwo.Call_NO = lstUpd.Call_NO
AND ttwo.Updated_dt = lstUpd.LastUpdate) t2

ON t1.Call_NO = t2.Call_NO