CODEWITHSUNDEEP
sqlsql-serverconditional-statementshaving
AlleXyS
AlleXyS

Reputation: 2598

sql having columnValue > 0 by a specific column

I can't find any solution for this table :

DocId | Item| Service              | Qty
1     | 10  | Energie activa       | 9.7140000000
1     | 18  | Tarif distributie JT | 9.7140000000
1     | 19  | Tarif transport TG   | 9.7140000000
1     | 20  | Tarif transport TL   | 9.7140000000
1     | 14  | Serviciu sistem      | 9.7140000000
1     | 15  | Cogenerare           | 9.7140000000

I need to select * from table for each DocId, where Item = 15 (this is ok). But, must have another condition, having Qty > 0 where Item is 18, 19 or 20

select *
from table
where item = 15

and something like :

having Qty > 0 where Item = 18 or Item = 19 or Item = 20

I don't need something like below code, because I have many inner joins in my entire query and thousands of DocId.

select *
from table
where item = 15 and (select qty from table where item = 18 and docId = 1) > 0

Upvotes: 1

Views: 56

Answers (2)

jle
jle

Reputation: 9489

You can use parentheses to combine conditions...

select *
from table
where item = 15 or (item in (18,19,20) and qty>0)

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can get the docids meeting both conditions using group by and having:

select docid
from table t
group by docid
having sum(case when item = 15 then 1 else 0 end) > 0 and
       sum(case when item in (18, 19, 20) and qty then 1 else 0 end) > 0;

You can get the original rows using in, exists, or join.

If you want just the row with item = 15, you can use exists directly:

select t.*
from table t
where t.item = 15 and
      exists (select 1
              from t t2
              where t2.docid = t.docid and
                    t2.item in (18, 19, 20) and
                    t2.qty > 0
             );

Upvotes: 3

Related Questions