dimanche 12 juin 2016

How to join a table to two different tables

I want to join a table to two tables and want result in below format,

ID    Name1    Name2     StatusOfName2Yearwise   Year 

what I have tried is,

select t1.ID, t1.Name1, t1a.Name2, isnull(t2.Status,'N') as 'StatusOfName2Yearwise',Year
from
    table_1 t1, table_1 t1a
    LEFT JOIN table_3 t3 on t1a.id=t3.f_id
    LEFT JOIN table_2 t2  on t1a.ID=t2.id,
    table_4 t4 LEFT JOIN table_2 t2a on t4.Year=t2a.Fyr
where
     t1.ID=t3.f_id 
group by
    t1.ID, t1.Name1, t1a.Name2, t2.Status ,Year
order by
    t1.Name1 

This query is giving status of Name2 but it is NOT yearwise and I want it Yearwise. Please help if anybody knows..

Adding output data I got from query,

 ID           Name1        Name2        StatusOfName2Yearwise     Year
 22           George       Julie              C                   2015           
 22           George       Julie              C                   2016       
 22           George       Julie              C                   2017       

expected result should be like below,

 ID           Name1         Name2        StatusOfName2Yearwise    Year
 22           George        Julie             N                   2015           
 22           George        Julie             N                   2016       
 22           George        Julie             C                   2017   

Aucun commentaire:

Enregistrer un commentaire