title: How to combine three XREF tables into one?
body: <p>I have three XREF tables. </p>
  
  <ol>
  <li><code>order_delivery_xref</code> : order_id (int), delivery_id (int)</li>
  <li><code>delivery_invoice_xref</code> : delivery_id (int), invoice_id (int)</li>
  <li><code>invoice_order_xref</code> : invoice_id (int), order_id (int)</li>
  </ol>
  
  <p>I now need to combine them into a common table, like so: </p>
  
  <p><code>order_delivery_invoice_xref</code> : order_id (int), delivery_id (int), invoice_id (int)</p>
  
  <p>Is there a way to do this using a query? or a procedure? or using a php script + mysql queries?</p>
  
  <p>Notes:</p>
  
  <ol>
  <li>An <code>delivery_id</code> always exists with an <code>order_id</code></li>
  <li>An <code>invoice_id</code> doesn't always exist, and needs to be <code>null</code> in the row, if missing</li>
  </ol>
  
  <hr>
  
  <p>Here's what i have tried:</p>
  
  <pre><code>SELECT order_id, delivery_id
  FROM order_delivery_xref
  WHERE 1
  </code></pre>
  
  <p>... and so on for all the three tables.</p>
  
  <p>Then I run a <code>foreach()</code> loop in <code>php</code> for the first array, check using <code>isset()</code> function for other arrays (replace <code>NULL</code> where <code>!isset()</code>), and create a table>tr>td in html.</p>
  
  <p>I'm not quite sure if this is the correct method. Can someone suggest a better method?</p>