CODEWITHSUNDEEP
sql
DonJuanEco
DonJuanEco

Reputation: 143

SQL query that will return rows that don't have a corresponding status, across ANY rows that have the same id?

I have the following table:

+====+=============+==========+=============+
| id | customer_id | order_id |   status    |
+====+=============+==========+=============+
|  1 |         999 |        1 | CREATED     |
+----+-------------+----------+-------------+
|  2 |         999 |        1 | IN_PROGRESS |
+----+-------------+----------+-------------+
|  3 |         222 |        2 | CREATED     |
+----+-------------+----------+-------------+
|  4 |         222 |        2 | IN_PROGRESS |
+----+-------------+----------+-------------+
|  5 |         222 |        2 | COMPLETE    |
+----+-------------+----------+-------------+
|  6 |         333 |        3 | CREATED     |
+----+-------------+----------+-------------+

How do I construct a SQL query that will return order_id's that don't have a corresponding 'COMPLETE' status, across ANY rows that have the same order_id?

I'd like to return:

order_id
1
3

order_id '2' shouldn't be returned, as 1 of the rows has a 'COMPLETE' status

I've tried a few things, but honestly I'm nowhere close.

SELECT DISTINCT order_id FROM my_table
WHERE status <> 'COMPLETE';

Thanks

Upvotes: 0

Views: 27

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270663

You can use aggregation:

SELECT order_id
FROM my_table
GROUP BY order_id
HAVING SUM(CASE WHEN status = 'COMPLETE' THEN 1 ELSE 0 END) = 0;

This counts the number of "COMPLETE" statuses for each order_id. The = 0 says that there are none.

Upvotes: 2

Related Questions