How to have multiple tables with multiple joins
I have three tables that I need to concatenate and get a combination of results. I've tried using left / right join, but they don't give the desired results.
For instance:
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 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've 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 lines:
id name id staffid mobilerightsid rights id rightname
null null null null null null 1 login
null null null null null null 2 View
Is it possible to do what I am trying to do, and if so, how?
thanks
a source to share
It is now clear from your comment that you want a cross join (including all lines from staff and mobiles). Something like this should do it
SELECT
*
FROM Staff, MobileRights
LEFT OUTER JOIN StaffMobileRights ON StaffMobileRights.StaffId = Staff.Id
The FROM clause indicates that we will include all rows from the Staff table and all rows from the MobileRights table. So the end result will contain the lines (staff * MobileRights).
To enter rows from StaffMobileRights, we also need to join this table. We use LEFT OUTER concatenation to ensure that we always include the left side (rows in the staff table), but we didn't worry too much if no row exists on the right side (the StaffMobileRights table). If no string exists for the join, then null values are returned.
a source to share
What you are probably asking is to see where there are no rights. In a rectangular style, results are always returned, this is the only way to represent it with a simple join:
From PaulG's query, I modified it slightly to always get everything from the STAFF table.
SELECT
*
FROM STAFF
RIGHT OUTER JOIN StaffMobileRights ON StaffMobileRights.StaffId = Staff.Id
INNER JOIN MobileRights ON MobileRights.Id = StaffMobileRights.MobileRightsId
a source to share