title: SQL JOIN from assignment table
body: <p>I have the following tables:</p>
  
  <p><code>rooms</code></p>
  
  <pre><code>rid | roomname   | ...
  ----------------------
  1   | Pantry     | ...
  2   | Conference | ...
  ...
  </code></pre>
  
  <p><code>employees</code></p>
  
  <pre><code>eid | lastname  | firstname
  ---------------------------
  1   | Zufall    | Rainer
  2   | Piper     | Peter
  .... 
  </code></pre>
  
  <p><code>assignments</code></p>
  
  <pre><code>aid | room | employee | weekday
  -------------------------------
  1   |  2   |   1      |   1
  2   |  2   |   1      |   2
  3   |  2   |   2      |   3
  4   |  2   |   1      |   4
  5   |  2   |   1      |   5
  6   |  1   |   2      |   1
  7   |  1   |   2      |   3
  8   |  1   |   2      |   5 
  </code></pre>
  
  <p>Now I want to write a query, listing me all rooms and their assignments in the following format:</p>
  
  <pre><code>roomname   | Monday | Tuesday | Wednesday | Thursday | Friday
  --------------------------------------------------------------
  Conference | Zufall | Zufall  |   Piper   |  Zufall  |  Zufall
  Pantry     | Piper  |  NULL   |   Piper   |   NULL   |  Piper
  </code></pre>
  
  <p>I wrote the query</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT r.roomname, e.lastname AS Monday
  FROM rooms AS r
  LEFT JOIN assignments AS a ON r.rid = a.room AND a.weekday = 1
  LEFT JOIN employees AS e ON a.employee = e.eid
  </code></pre>
  
  <p>what gives me all assignments for monday, but how can I combine that with the assignments on the other weekdays?</p>
  
  <p>Thanks in advance.
  Lars</p>