I need to write a sql query.
TblSettl
Cols - polnr1, polnr2, classtype
101, 1,type1
102, 2,type2
103,3,type1
104,4,type1
TblInclusions
Cols- polnr, date
101, 01/12/20
101, 30/01/21
3, 02/01/21
I need to pull list of settlements where
If class type is type1 then if match is there in tblinclusions and any record has calldate >01/01/21
Setllemnt polnr1 or 2 can match with inclusions table
If class type 2 then no check required in the inclusions table
Result expected
101
102
103
My query
Select s.Polnr
From tblsettl s
Left outerjoin tblinclusions inc
On case when s. Polnr1=inc.polnr then s. Polnr1
When s. Polnr2=inc.polnr then s. Polnr2
End = inc. Polnr
Where
(S.type=type1 and inc. Polnr is not null and inc. Date>'01/01/21')
Or
(S.type <>type1) --no check on inclusions required.
Problem is because there are 2 records for 101 in inclusions I get
101
101
102
103
I don't want to use subqueries. Any way to wrote using joins pls help.