CODEWITHSUNDEEP
postgresqlrelational-division
Blair Anderson
Blair Anderson

Reputation: 20171

Query where ALL associated records have attribute value X

I have a query that counts associated records AND associated status

SELECT
  orders.id,
  SUM(CASE WHEN s.shipment_status='CLOSED' THEN 1 ELSE 0 END) as closed,
  COUNT(*) as shipment_count
FROM orders as po
JOIN shipments as s ON s.order_id = po.id 
GROUP BY po.id

I am attempting to query all orders, where all the shipments are CLOSED.

Essentially looking at the above, just returning when closed = shipment_count .

If I add an AND clause to the join then it will simply limit the number of shipments.

Upvotes: 0

Views: 140

Answers (1)

Blair Anderson
Blair Anderson

Reputation: 20171

I figured this out with a HAVING clause, which doesn't use the select attrs. 

SELECT
  orders.id
FROM orders as po
JOIN shipments as s ON s.order_id = po.id
GROUP BY po.id
HAVING SUM(CASE WHEN s.shipment_status='CLOSED' THEN 1 ELSE 0 END) = COUNT(*)

Leaving my answer up in case it helps others. Maybe better answers available to come from the community.

Upvotes: 1

Related Questions