title: MySql JOIN 3 tables and get child rows with same values
body: <p>I have 3 tables: order, item and render.</p>
  <p>1 order can have multiple items and each item has one render entry with status.</p>
  <p>I want to have the order id's and date which have ALL its items with rendered 'done'.</p>
  <p>In the example it would be the order 1 only, which has its 2 items (1 and 2) with status done in render table</p>
  <p>order table</p>
  <pre><code>id| date
  1 | 2021-12-10
  2 | 2021-12-11
  </code></pre>
  <p>item table</p>
  <pre><code>id|order_id
  1 | 1
  2 | 1
  3 | 2
  4 | 2
  </code></pre>
  <p>render table</p>
  <pre><code>id|item_id| status
  1 |1      | done
  2 |2      | done
  3 |3      | done
  4 |4      | running
  </code></pre>
  <p>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):</p>
  <pre><code>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 &lt;&gt; 'done')
  AND r2.item_id IS NULL;
  </code></pre>
  <p>Any ideas? thank you in advance!</p>