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.

For example:

Table 1 - STAFF

id      name
1       John
2       Fred

Table 2 - STAFFMOBILERIGHTS

id      staffid     mobilerightsid      rights
--this table is empty--

Table 3 - MOBILERIGHTS

id      rightname
1       Login
2       View

and what I need is this as the result...

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

I have tried the following :

SELECT *
  FROM STAFFMOBILERIGHTS SMR
  RIGHT JOIN STAFF STA
  ON STA.STAFFID = SMR.STAFFID
  RIGHT JOIN MOBILERIGHTS MRI
  ON MRI.ID = SMR.MOBILERIGHTSID

But this only returns two rows as follows:

id      name    id  staffid mobilerightsid  rights  id  rightname
null    null    null    null    null        null    1   login
null    null    null    null    null        null    2   View

Can what I am trying to achieve be done and if so how?

Thanks