title: How to have multiple tables with multiple joins
body: <p>I have three tables that I need to join together and get a combination of results. I have tried using left/right joins but they don't give the desired results.</p>
  
  <p>For example:</p>
  
  <p>Table 1 - STAFF</p>
  
  <pre><code>id      name
  1       John
  2       Fred
  </code></pre>
  
  <p>Table 2 - STAFFMOBILERIGHTS</p>
  
  <pre><code>id      staffid     mobilerightsid      rights
  --this table is empty--
  </code></pre>
  
  <p>Table 3 - MOBILERIGHTS</p>
  
  <pre><code>id      rightname
  1       Login
  2       View
  </code></pre>
  
  <p>and what I need is this as the result...</p>
  
  <pre><code>id  name    id  staffid mobilerightsid  rights  id  rightname
  1   John    null    null    null        null    1   login
  1   John    null    null    null        null    2   View
  2   Fred    null    null    null        null    1   login
  2   Fred    null    null    null        null    2   View
  </code></pre>
  
  <p>I have tried the following :</p>
  
  <pre><code>SELECT *
    FROM STAFFMOBILERIGHTS SMR
    RIGHT JOIN STAFF STA
    ON STA.STAFFID = SMR.STAFFID
    RIGHT JOIN MOBILERIGHTS MRI
    ON MRI.ID = SMR.MOBILERIGHTSID
  </code></pre>
  
  <p>But this only returns two rows as follows:</p>
  
  <pre><code>id      name    id  staffid mobilerightsid  rights  id  rightname
  null    null    null    null    null        null    1   login
  null    null    null    null    null        null    2   View
  </code></pre>
  
  <p>Can what I am trying to achieve be done and if so how?</p>
  
  <p>Thanks</p>