title: MySql list direct and indirect child records
body: <p>I have a 3 tables DB schema for handling transport items. A transport can contain multiple boxes and multiple items. Besides this a box also can contain multiple items. So an item can be attached to the transport directly and indirectly.</p>
  
  <p>A simplified DDL is this:</p>
  
  <pre><code>CREATE TABLE transport (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `transport_number` varchar(50) NOT NULL,
    `transport_date` datetime NOT NULL
  )
  
  CREATE TABLE box (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(50) NOT NULL,
    `transport_id` int(11) NOT NULL
  )
  
  CREATE TABLE item (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(50) DEFAULT NULL,
    `box_id` int(11) DEFAULT NULL,
    `transport_id` int(11) DEFAULT NULL
  )
  </code></pre>
  
  <p>I try to implement a query that select all transport with the box and item count. Something like this:</p>
  
  <p>Expected result:</p>
  
  <pre><code>TRANSPORT_ID | TRANSPORT_NUMBER | BOX_COUNT | ITEM_COUNT
  --------------------------------------------------------
  1            | T1               | 2         | 3
  2            | T2               | 1         | 2
  </code></pre>
  
  <p><strong>Sample data</strong></p>
  
  <p>Transport:</p>
  
  <pre><code>ID     | TRANSPORT_NUMBER | TRANSPORT_DATE
  ------------------------------------------
  1      | T1               | 2018-11-06
  2      | T2               | 2018-11-06
  </code></pre>
  
  <p>Box:</p>
  
  <pre><code>ID     | TITLE            | TRANSPORT_ID
  ------------------------------------------
  1      | B-1              | 1
  2      | B-2              | 1
  3      | B-3              | 2
  </code></pre>
  
  <p>Item:</p>
  
  <pre><code>ID     | TITLE            | BOX_ID    | TRANSPORT_ID
  ----------------------------------------------------
  1      | I-1              | 1         | NULL
  2      | I-2              | 2         | NULL
  3      | I-3              | NULL      | 1
  4      | I-4              | 3         | NULL
  5      | I-5              | 3         | NULL
  </code></pre>