Reputation: 928
I am trying to write a query for MS SQL Server which returns the container_parent_id
s that have quantity of 0:
Container_id | Container_name | container_parent_id | quantity | alive
----------------------------------------------------------------------
1 | ContA | 12345 | 2 | 1
2 | ContB | 12345 | 1 | 1
3 | ContC | 12345 | 0 | 1
4 | ContD | 12345 | 1 | 1
5 | ContA | 23456 | 1 | 1
6 | ContB | 23456 | 0 | 1
7 | ContC | 23456 | 1 | 1
8 | ContD | 34567 | 0 | 1
9 | ContE | 34567 | 0 | 1
In the above data, only ContD and ContE should be returned for 34567.
If I group by container_parent_id
and use WHERE quanity = 0
, the result includes other rows.
This is what I have been trying:
SELECT count(), container_parent_id from Container where quantity = 0 GROUP BY container_parent_id HAVING count() > 0
resulting in:
3 | ContC | 12345 | 0 | 1
6 | ContB | 23456 | 0 | 1
8 | ContD | 34567 | 0 | 1
9 | ContE | 34567 | 0 | 1
Problem with running this query or what Renu/Stan suggested is, it returns those containers that have quantity as 0 - as a subset. But, I do not want the first two as they have other containers with quantities 1 or 2 for the same container_parent_id
. My final output should just be:
8 | ContD | 34567 | 0 | 1
9 | ContE | 34567 | 0 | 1
I.e. every container_parent_id that which has only records with quantity = 0.
Upvotes: 0
Views: 728
Reputation: 14470
SELECT T.*
FROM Container t
INNER JOIN (
SELECT container_parent_id,SUM(quantity) Tot
FROM Container
GROUP BY container_parent_id) g ON T.container_parent_id = G.container_parent_id
WHERE G.Tot=0
Upvotes: 1
Reputation: 3683
Something like this should work:
SELECT *
FROM Containers
WHERE
container_parent_id IN (
SELECT container_parent_id
FROM Containers
GROUP BY container_parent_id
HAVING sum(quantity) = 0
);
First select all the parent IDs that have a total of 0 quantity and then use that as a nested query to get all the container names that have those IDs as parents.
Upvotes: 2
Reputation: 119
Simply Try this,
SELECT container_parent_id FROM Container WHERE quantity=0
Upvotes: -1