How to have multiple tables with multiple joins

Question

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

Answer 1

From your comment its now clear you want a cross join (include all rows from staff and mobilerights). Something like this should do it

SELECT 
*
FROM Staff, MobileRights
LEFT OUTER JOIN StaffMobileRights ON StaffMobileRights.StaffId = Staff.Id

The FROM clause specifies that we will be including all rows from the Staff table, and all rows from the MobileRights table. The end result will therefore contain (staff * MobileRights) rows.

To bring in rows from StaffMobileRights then we need a join to that table also. We use a LEFT OUTER join to ensure that we always include the left side (rows in the staff table) but we arent too bothered if no rows exist on the right side (StaffMobileRights table). If no row exists for the join then null values are returned.

Answer 2

What you are probably asking is to see null where is no rights. In the rectangular style that results are always returned, this is the only way to represent it with a simple join:

From PaulG's query i changed it a bit to always get everything form the STAFF table.

SELECT 
*
FROM STAFF
RIGHT OUTER JOIN StaffMobileRights ON StaffMobileRights.StaffId = Staff.Id
INNER JOIN MobileRights ON MobileRights.Id = StaffMobileRights.MobileRightsId