SQL Server : joining a table to itself?

Question

I'm having issues joining a table on itself, or actually on a complex query already involving this table. Let me explain what exactly I mean by it.

Currently I have this select:

SELECT 
    t.maticna, t.clientNo, t.letoZr, t.letoZrChr, t.sort, 
    t.tipOcene, t.bonitetniModel, t.datumOtvoritve, t.DatumKLJ,
    t.ocm1solor_LT, t.ocm1solo_LT, t.ocm1solor_ST, t.ocm1solo_ST 
FROM 
    (SELECT 
         a.maticna, a.clientNo,  a.letoZr, a.letoZrChr, a.sort, 
         a.tipOcene, a.bonitetniModel, a.datumOtvoritve,
         im.PKModul1_LT as ocm1solor_LT, im.PDModul1_LT*100 as ocm1solo_LT, 
         im.PKModul1_ST as ocm1solor_ST, im.PDModul1_ST*100 as ocm1solo_ST, 
         im.DatumKLJ,
         ROW_NUMBER() OVER (PARTITION BY a.letoZrChr ORDER BY datumOtvoritve DESC, im.DatumKLJ DESC) AS rn
     FROM 
         #osnovna a 
     LEFT JOIN 
         airb.bm.IzhodModeli im ON a.clientNo = im.KomitentID 
                                AND im.ReferencniDatum = a.datumOtvoritve 
                                AND a.tipocene = im.tipocene) t
WHERE 
    t.rn = 1
ORDER BY 
    t.DatumKLJ DESC

What it does is, is returns these 5 rows:

maticna clientNo letoZr letoZrChr sort  tipOcene    bonitetniModel  datumOtvoritve  DatumKLJ    ocm1solor_LT    ocm1solo_LT ocm1solor_ST    ocm1solo_ST
5046432 975145  2019    ML2019    4       2              GD          2019-06-30      43900             4    0,868   3   0,565
5046432 975145  2018    ZR2018    3       0              GD          2018-12-31      43900             8    34,425  8   24,269
5046432 975145  2017    ZR2017    2       0              GD          2017-12-31      43612             5    2,622   4   1,173
5046432 975145  2016    ZR2016    1       0              GD          2016-12-31      43355             4    1,405   3   0,588
5046432 975145  2019    PL2019    5       3              GD          2019-12-31                      NULL   NULL    NULL    NULL    NULL

Now, long story short, I want the select to also show the last 4 columns of the table im (ocm1solor_LT, ocm1solo_LT, ocm1solor_ST, ocm1solo_ST ), where:

if tipOcene is currently 2, then I want to add to that row the 4 columns written above where tipOcene=6, and DatumKLJ, DatumOtvoritve and clientNo are the same.

if tipOcene is currently 0, then I want to add to that row the same 4 columns, but tipOcene=1.

and if tipOcene is 3, then I want to add to that row the same 4 columns, but with tipOcene=7.

The problem I'm having is, no matter how I try to JOIN the "im" table on this, I keep ending up with more results. So if anybody has any suggestions how to make a JOIN, in the manner that it wont add any rows, but it'll only add the 4 columns written above to the end of the current rows, except for a different tipOcene, it'd be much appreciated.

I hope the instructions of what I want are at least a bit clear lol, if not, please do feel free to ask and I'll be happy to explain, since I'm really struggling with it...

Answer 1

Thanks Keith, a left join with cases on WHERE actually did the job, not sure how I missed that earlier lol.