How to have multiple tables with multiple joins
- by williamsdb
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
