MySql JOIN 3 tables and get child rows with same values-CodePudding

I have 3 tables: order, item and render.

1 order can have multiple items and each item has one render entry with status.

I want to have the order id's and date which have ALL its items with rendered 'done'.

In the example it would be the order 1 only, which has its 2 items (1 and 2) with status done in render table

order table

id| date
1 | 2021-12-10
2 | 2021-12-11

item table

id|order_id
1 | 1
2 | 1
3 | 2
4 | 2

render table

id|item_id| status
1 |1      | done
2 |2      | done
3 |3      | done
4 |4      | running

I would like to have it with JOIN statements. I've tried this but it does not work like expected (it returns both, order 1 and 2):

SELECT o.id, o.date
FROM order AS o
JOIN item AS i ON o.id = i.order_id
JOIN render r1 ON (i.`id` = r1.item_id AND r1.status = 'done')
LEFT JOIN render r2 ON (i.`id` = r2.item_id AND r2.status <> 'done')
AND r2.item_id IS NULL;

Any ideas? thank you in advance!

CodePudding user response：

SELECT t1.id
     , t1.date
  FROM order
       t1
 WHERE t1.id NOT IN
       (
         SELECT o.id
           FROM order
                o
           JOIN item
                i
             ON i.order_id = o.id
           JOIN render
                r
             ON r.item_id = i.id
          WHERE r.status <> 'done'
       )

The choice of some column names is unfortunate, making the join conditions painful for a maintainer to read: it is not immediately obvious at a glance what kind if IDs the id columns refer to.

I would rename the following columns:

TABLE  COLUMN_NAME BETTER_COLUMN_NAME
------ ----------- ------------------
order  id          order_id
order  date        order_date
item   id          item_id
render id          render_id
render status      render_status

I also disagree with the o / i / r table aliases, but arguments are in room 12A, just along the corridor.

CodePudding user response：

Here I post what it eventually worked best for me in case it helps anybody :)

SELECT o.id, o.date
FROM order AS o
JOIN item AS i ON o.id = i.order_id
LEFT JOIN render r ON (r.item_id = i.id)
GROUP BY o.id
HAVING GROUP_CONCAT(DISTINCT IFNULL(r.status, "NULL")) = 'done';

In the HAVING clause I concatenate the statuses, remove all duplicates and check that only “done” is there. Since I needed to have a LEFT JOIN against render I explicitly set to “NULL“ the returned null rows.