How to select all orders with all items completed?

Question

I have the following tables:

CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `billing_profile_id` int(10) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `currency` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `valid_until` timestamp NULL DEFAULT NULL
)

And:

CREATE TABLE `items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(10) unsigned NOT NULL,
  `status` enum('pending','processing','completed','on-hold','cancelled'),
  `code` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `quantity` int(10) unsigned NOT NULL DEFAULT '1',
  `term` int(10) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `discounts` int(10) unsigned NOT NULL,
  `total` int(10) unsigned NOT NULL
)

How do I SELECT only the orders that have all of its items with status completed?

I tried with this:

SELECT o.* FROM 
FROM orders o INNER JOIN items i on o.id=i.order_id
GROUP BY o.id, i.status
HAVING i.status = 'completed'

But that returns only the orders that have some items completed, not all.

EDIT: An order with no items is also a valid result.

update you question add a proper data sample and the expected result .. add also you the code you tried .. — ScaisEdge, Mar 19 '18 at 13:41
Possible duplicate of [SQL: Select from one table matching criteria in another?](https://stackoverflow.com/questions/5446778/sql-select-from-one-table-matching-criteria-in-another) — Sirmyself, Mar 19 '18 at 13:42
Thanks for your input. I edited the question with what I already tried — Cabezota, Mar 19 '18 at 13:44
By the way, I ran both CREATE TABLE in MySQL 5.5 and... they fail :D You need to add PRIMARY KEY to use AUTO_INCREMENT. Just sayin' — The Impaler, Mar 19 '18 at 13:53
I suspect that there are better answers out there than the selected one. — Strawberry, Mar 19 '18 at 14:00
@Cabezota Yes - it's just a lot more query than seems strictly necessary. — Strawberry, Mar 19 '18 at 14:08

score 4 · Answer 1 · answered Mar 19 '18 at 13:45

Try this:

SELECT *
FROM   orders
WHERE  EXISTS (SELECT *
               FROM   items
               WHERE  orders.id = items.order_id
                 AND  items.status = 'completed')
  AND  NOT EXISTS (SELECT *
                   FROM   items
                   WHERE  orders.id = items.order_id
                     AND  items.status <> 'completed')

The Impaler · Accepted Answer · 2018-03-19T13:51:16.827

1

Easy:

select * from orders
  where id not in (
    select order_id from items where status <> 'completed'
  )

edited Mar 19 '18 at 13:51

answered Mar 19 '18 at 13:46

The Impaler

45,731
9
39
76

I guess 'order_id' is the FK on items. So: select order_id from items... – Robert Kock Mar 19 '18 at 13:50
This also returns the orders not having any item at all. Not sure whether that's the desired behaviour – Robert Kock Mar 19 '18 at 13:54
Yep, but I guess "all" is valid for zero rows too... Well, the question is not that clear. – The Impaler Mar 19 '18 at 13:55
I'm selecting this answer because it is as efficient as the others and, in my opinion, simpler – Cabezota Mar 19 '18 at 14:26

Strawberry · Answer 3 · 2018-03-19T14:29:44.930

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  `id` SERIAL PRIMARY KEY
  );

INSERT INTO orders VALUES
(100),(101);

DROP TABLE IF EXISTS items;

CREATE TABLE `items` (
  `id` SERIAL PRIMARY KEY,
  `order_id` int(10) unsigned NOT NULL,
  `status` enum('pending','processing','completed','on-hold','cancelled')
);

INSERT INTO items VALUES
(1,100,'completed'),
(2,100,'completed'),
(3,100,'completed'),
(4,101,'completed'),
(5,101,'processing');


SELECT DISTINCT o.* 
           FROM orders o
           LEFT 
           JOIN items i 
             ON i.order_id = o.id 
            AND i.status <> 'completed' 
          WHERE i.id IS NULL;

+-----+
| id  |
+-----+
| 100 |
+-----+

...or...

SELECT DISTINCT o.* 
           FROM orders o
          WHERE NOT EXISTS (SELECT 1 
                              FROM items i 
                             WHERE i.order_id = o.id 
                               AND i.status <> 'completed');

This answer seems to have equivalent cost to the previous ones posted already. If you think is cheaper, can you post the execution plan for it? — The Impaler, Mar 19 '18 at 14:14
Just checked it, and it runs a full table scan on both tables. It's not faster. Here's the plan: id select_type table type Extra -- ----------- ----- ---- ----- 1 SIMPLE o ALL Using temporary 1 SIMPLE i ALL Using where; Not exists; Distinct — The Impaler, Mar 19 '18 at 14:21

How to select all orders with all items completed?

3 Answers3