question

NewbieNewbie-0350 avatar image
0 Votes"
NewbieNewbie-0350 asked MelissaMa-msft commented

Sql query help

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.

sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can you try a "select distinct..."?

0 Votes 0 ·

Hi @NewbieNewbie-0350,

Could you please validate all the answers so far and provide any update?

If all are not working, please provide more sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @NewbieNewbie-0350,

Please refer below:

 select distinct a.polnr1 
 from TblSettl a, TblInclusions b 
 where (a.classtype='type1' and (a.polnr1=b.polnr or a.polnr2=b.polnr) and b.date>'01/01/2021')
 or a.classtype<>'type1'

OR

 select a.polnr1
 from TblSettl a, TblInclusions b 
 where (a.classtype='type1' and (a.polnr1=b.polnr or a.polnr2=b.polnr) and b.date>'01/01/2021')
 union 
 select polnr1 from TblSettl where classtype<>'type1'
 order by polnr1

Output:

 polnr1
 101
 102
 103

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RyanAbbey-0701 avatar image
0 Votes"
RyanAbbey-0701 answered

What is your actual need? Why is 102 present in your output?

 Select s.Polnr
 From tblsettl s
 Left outer join tblinclusions inc
 On (S.type='type1' and s.Polnr1=inc.polnr and inc.Date>'01/01/21')
  or s. Polnr2=inc.polnr 

Because you're doing a left outer, you're getting everything from "s" regardless, what is it you actually want? maybe an inner join for my query?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KevinNelson-5334 avatar image
0 Votes"
KevinNelson-5334 answered KevinNelson-5334 rolled back

@NewbieNewbie-0350

Select DISTINCT 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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.