SQL Join Problem

[Nishad]

Hi I have 2 tables Table A and Table B I want to join these tables. The relation between these tables is RegNo The first table stores the Trudent details and second table is a history table The regNo in the first table is primary key and second table is forign key I want to Join all the data in the these two tables based on Date and Last updated time I want the result like the following pls help me to find a soln RegNo Name Class Date 12ABC ABC 10 3/2/2010 13ABC DCB 1 3/2/2010 14DFG DFG 12 3/2/2010 15FHG FHG 5 3/2/2010 16RTY RTY 8 3/3/2010 Table B Id RegNo LastUpdated 1 12ABC 3/2/2010 12:30 2 12ABC 3/2/2010 13:00 3 12ABC 3/2/2010 16:00 4 13BC 3/2/2010 12:00 Result RegNo Name Class LastUpdated 12ABC ABC 10 3/2/2010 16:00 13ABC DCB 1 3/2/2010 12:00 14DFG DFG 12 NULL 15FHG FHG 5 NULL

[Mycroft Holmes]

Here is an article [^]that will help you understand what you want

Never underestimate the power of human stupidity RAH

[Pranay Rana]

Hope below code will resolve you problem select a.regno, a.name , a.class, b.date from table a left outer join ( select * from tableb wehre id = (select max(id) from tablb group by regno)) b on a.regno = b.regno

[masoumeh2010]

Hi, This query dose not answer because RegNo in Table_B in Id 4 dose not equal with RegNo with Table_A, But if it's correct this is answer select a.RegNo,a.Name,count(a.Class),b.LastUpdated from Table_A as a left join Table_B as b on a.RegNo=b.RegNo where (b.Id between 3 and 4 ) or (a.RegNo Not In('16RTY')and a.RegNo In('14DFG','15FHG')) group by a.RegNo,a.Name,b.LastUpdated