MS SQL query with multiple search criteria across rows

Question

I have below table and SQL query written, this query should not return any result but its returning ID = 1 , what is wrong with the SQL query? Can anyone please help?

** Note balance data type is decimal rest are varchar

ID  code    balance level
1   C   150.00  
1   P   40027.42    F
1   P   40027.42    F

select distinct ID from table
(
(code = 'P' and balance = 40027.42 and level = 'F') or
(code  = 'C' and balance = 151.00 )
)
group by ID
having count(ID) >=2

Based on your having clause of count(ID) >=2, this query is returning the id for rows that meet the where clauses and have more than 1 row, which would be ID = 1. Since you have two rows for ID = 1 that meet the clause of (code = 'P' and balance = 40027.42 and level = 'F') Also, distinct is not needed for this query. — SqlZim
– SqlZim, Commented Sep 25, 2017 at 12:19
Having clause is ok but questions is about the balance value for code C , table has 150.00 and my query has 151.00 so query should not return any result but it still it’s ignoring the 151.00 — AHS
– AHS, Commented Sep 25, 2017 at 12:40
I see that, but you are ignoring the fact that you have two rows that meet this crtieria: (code = 'P' and balance = 40027.42 and level = 'F') Since you have two rows that meet that critieria, it passes the having clause of having count(ID) >=2. greater than or equal to 2 — SqlZim
– SqlZim, Commented Sep 25, 2017 at 12:41
My bad , I see that now. Thanks ... is there a way to modify this query to consider only one record by ID so that in this query second set of condition with code C also gets evaluated by query ? — AHS
– AHS, Commented Sep 25, 2017 at 13:07

SqlZim · Accepted Answer · 2017-09-28 12:00:29Z

1

If you do not want to count the same code twice, you can use count(distinct code):

select ID 
from t
where (code = 'P' and balance = 40027.42 and level = 'F') 
  or (code  = 'C' and balance = 151.00 )
group by ID
having count(distinct code) >=2

If you want to only count a distinct set of values once, you can use a derived table/subquery to select distinct rows:

select ID 
from (
  select distinct id, code, balance, level 
  from t
  ) as s
where (code = 'P' and balance = 40027.42 and level = 'F') 
  or (code  = 'C' and balance = 151.00 )
group by ID
having count(ID) >=2

rextester demo for both: http://rextester.com/LBKO57534

answered Sep 28, 2017 at 12:00

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MS SQL query with multiple search criteria across rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related