title: SQL query for multiple (one-to-many) tables in mysql database
body: <p>I have a database with tours (group city tours with guide). What I need is a SQL query (for a mysql 5.1.54 database) that will eventually give me a PHP array with the information out of multiple tables combined. I'm able to accomplish this by doing a query just for the first 3 tables and then adding the information in table 4 and 5 within a foreach loop.</p>
  
  <p>But, I need to use the query for searching/filtering (like: show all reservations where partner has id 9) so I need SQL to select the corresponding reservations.</p>
  
  <ol>
  <li>Reservations with no partners (table 4) and/or guides (table 5) must be included.</li>
  <li>All partners and guides must be included in the result (reservation can have more partners/guides)</li>
  <li>All information in table 4 and 5 must be included (like the status from table 4).</li>
  </ol>
  
  <p>A simplified version of the database:</p>
  
  <p><strong>Table 1: reservations:</strong></p>
  
  <pre><code>id    id_client    id_tour
  1     22            6
  2     23            5
  </code></pre>
  
  <p><strong>Table 2: clients (one reservation has one client):</strong></p>
  
  <pre><code>id    name
  22    John
  23    William
  </code></pre>
  
  <p><strong>Table 3: tours (one reservation has one tour)</strong></p>
  
  <pre><code>id    name
  5     big tour
  6     small tour    
  </code></pre>
  
  <p><strong>Table 4: partners (one reservation can have multiple partners):</strong></p>
  
  <pre><code>id    id_reservation    id_partner_type    id_partner    status
  34    1                 9                  16            1
  35    1                 9                  17            0
  </code></pre>
  
  <p><strong>Table 5: guides (one reservation can have multiple guides):</strong></p>
  
  <pre><code>id    id_reservation    id_guide
  18    1                 14
  19    1                 15
  </code></pre>
  
  <p>I have tried to work this out but I just can not get a query that does the job. I used GROUP_CONCAT to get the multiple partner and guide id's. Biggest problem I have is not being able to include the reservations that have no partners and/or guides like reservation 2. </p>