MS Access multi (INNER, LEFT & RIGHT) JOIN request
Okay, that's what. I have the following tables:
> YEARS
------------------------------
ID YEAR ACTUAL
------------------------------
1 2014-15 TRUE
2 2015-16 FALSE
> SHOPS
------------------------------
ID NAME ...
------------------------------
1 ThisShop ...
> ITA
------------------------------
ID YEAR_ID SHOP_ID
------------------------------
1 1 1
2 1 2
...
> INSPECTORS
------------------------------
ID INSPECTOR
------------------------------
1 M. Black
2 M. White
3 M. Brown
...
> ITA_INSPECTORS
-------------------------------------------------------
ID ID_ITA ID_INSPCTR StartDate EndDate
-------------------------------------------------------
Here is the thing I want to query to display everything INSPECTORS
, whether listed in lists or not in ITA_INSPECTORS
for SHOPS ID = 1 AND YEARS ID = 1
. If the inspector is present in the table ITA_INSPECTORS
, show the start and end dates, if not, show without dates.
Please note that you may not be ITA_ID
in ITA_INSPECTORS
the table for the selected store (imagine ITA_INSPECTORS
the table is empty, I wouls still need to see all the names of Inspector General).
The table INSPECTORS
is the static data for building the table ITA_INSPECTORS
.
I tried this query:
SELECT * FROM ((ITA
INNER JOIN YEARS ON ITA.ID_YEAR = YEARS.ID)
LEFT JOIN ITA_INSPECTORS ON ITA.ID = ITA_INSPECTORS.ID_ITA)
RIGHT JOIN INSPECTORS ON ITA_INSPECTORS.ID_INSPCTR = INSPECTORS.ID
WHERE ITA.SHOP_ID = 1 AND ((YEARS.ACTUAL) = True);
It works until I add a suggestion RIGHT JOIN
, at which point I get an error Join expression not supported
.
Can anyone guide me on the correct way to do this?
source to share
Well one solution is to split the query so that it doesn't have these conflicting joins So create a query like q1
SELECT *
FROM (
(
ITA INNER JOIN YEARS ON ITA.ID_YEAR = YEARS.ID
) LEFT JOIN ITA_INSPECTORS ON ITA.ID = ITA_INSPECTORS.ID_ITA
)
and then create a second query to make the connection you want
SELECT INSPECTORS.ID, INSPECTORS.INSPECTOR, q1.*
FROM INSPECTORS LEFT JOIN q1 ON INSPECTORS.ID = q1.ID_ITA;
source to share