title: Select query - Joining same table multiple times to different columns
body: <p>I'm a SQL novice trying to write a select query that involves a join of multiple tables, with one table multiple times.</p>
  
  <p>The relationships are shown below:
  <a href="https://i.stack.imgur.com/PbDhc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PbDhc.png" alt="enter image description here"></a></p>
  
  <p>The output of the query I'm trying to achieve is this:</p>
  
  <p><code>Truck_ID|Pushbutton_ID|Trailer01_ID|Trailer02_ID|Trailer03_ID|Trailer04_ID|Trailer05_ID</code></p>
  
  <p>The joining of the tables <code>Trucks</code> and <code>Pushbutton_Assignments</code> is straightforward. The difficulty is joining the <code>Trailer_Assignment</code> table multiple times.</p>
  
  <p>The structure of <code>Trailer_Assignments</code> is:</p>
  
  <p><code>Trailer_ID|Truck_ID|Order</code></p>
  
  <p>The <code>Trailer_ID</code> and <code>Truck_ID</code> fields have foreign key relationships with other tables. The <code>TrailerXX_ID</code> fields of the query are related to the <code>Order</code> field of the <code>Trailer_Assignment</code> table; all records with <code>Order == 1</code> have <code>Trailer_ID</code> put in <code>Trailer01_ID</code>, records with <code>Order == 2</code> to <code>Trailer02_ID</code> and so on. </p>
  
  <p>I based my query from <a href="https://stackoverflow.com/questions/12561733/mysql-how-do-i-join-same-table-multiple-times?lq=1">here</a>. It works for a single column but Access (I think) doesn't like subsequent columns.</p>
  
  <p>Working Query code (single column):</p>
  
  <p><code>SELECT Trucks.ID, 
     Pushbutton_Assignment.Pushbutton_ID,
     Trailer_Assignment.Trailer_ID AS Trailer01_ID,
     Trailer_Assignment.Trailer_ID AS Trailer02_ID,
     Trailer_Assignment.Trailer_ID AS Trailer03_ID,
     Trailer_Assignment.Trailer_ID AS Trailer04_ID,
     Trailer_Assignment.Trailer_ID AS Trailer05_ID
  FROM (Trucks LEFT JOIN Pushbutton_Assignment ON Trucks.ID = Pushbutton_Assignment.Truck_ID) LEFT JOIN Trailer_Assignment ON Trucks.ID = Trailer_Assignment.Truck_ID AND Trailer_Assignment.Order = 1;</code></p>
  
  <p>EDIT:</p>
  
  <p>Non-working Query code (two column):</p>
  
  <p><code>SELECT Trucks.ID, 
     Pushbutton_Assignment.Pushbutton_ID,
     Trailer_Assignment.Trailer_ID AS Trailer01_ID,
     Trailer_Assignment.Trailer_ID AS Trailer02_ID,
     Trailer_Assignment.Trailer_ID AS Trailer03_ID,
     Trailer_Assignment.Trailer_ID AS Trailer04_ID,
     Trailer_Assignment.Trailer_ID AS Trailer05_ID
  (((FROM (Trucks LEFT JOIN Pushbutton_Assignment ON Trucks.ID = Pushbutton_Assignment.Truck_ID))
  LEFT JOIN Trailer_Assignment ON (Trucks.ID = Trailer_Assignment.Truck_ID AND Trailer_Assignment.Order = 1))
  LEFT JOIN Trailer_Assignment ON (Trucks.ID = Trailer_Assignment.Truck_ID AND Trailer_Assignment.Order = 2));</code></p>
  
  <p>This is using Access 2003.</p>