Pick up attributes from other tables in a query

[oemar00]

Hello,
Please refer to the attached file.
I have five tables of the five three are related by primary key tblA, tblAB, tblB I can list elements in these tables through the following query which I name qryA_AB_B:
SELECT tblA.ID_A
, tblA.Attr01_A
, tblA.Attr02_A
, qryMain.ID_B
, qryMain.Attr01_B
, qryMain.Attr02_B
FROM tblA LEFT JOIN (
SELECT tblAB.ID_A
, tblAB.ID_B
, tblB.Attr01_B
, tblB.Attr02_B FROM tblAB INNER JOIN tblB
ON tblB.ID_B = tblAB.ID_B) AS qryMain


ON tblA.ID_A = qryMain.ID_A
ORDER BY 1, 4;
However tblA has attributes in tables tblA01 and tblA02 which I should easily retrieve by adding
RIGHT JOIN (tblA02 RIGHT JOIN qryA_AB_B
ON [tblA02].A02_ID = [qryA_AB_B].Attr02_A)
ON [tblA01].A01_ID = [qryA_AB_B].Attr01_A;
To the above query so the full query looks like:
SELECT ID_A
, Attr01_A
, Attr02_A
, ID_B
, Attr01_B
, Attr02_B
FROM qryA_AB_B
RIGHT JOIN (tblA02 RIGHT JOIN qryA_AB_B
ON [tblA02].A02_ID = [qryA_AB_B].Attr02_A)
ON [tblA01].A01_ID = [qryA_AB_B].Attr01_A;
but Access would not save the query which I name qryFinal and keep telling me syntax error on JOIN [tblA01].A01_ID.
Anybody sees anything wrong here.

[Dal Jeanis]

First, you're missing the ON keyword in the inner-most right join.

Second, I'm not sure why you're mixing left and right joins in the same query in this case.
The working LEFT JOIN query looks like this:

tblA -> (tblAB = tblB)

You want everything from tlbA, whether or not there is a matching AB record, but only the matching records from tblAB and tblB.

You also want to pick up some more data from tblA01 and tblA02, that match the same tblA record that you already have above, right? So, you should be able to use all left joins.

(((tblA -> tblA01) -> tblA02) -> (tblAB = tblB))

Which would look something like this:

SELECT (fields)
FROM
(((TblA LEFT JOIN tblA01 ON tblA.attr1 = tblA01.pk
        )
        LEFT JOIN tblA02 ON tblA.attr2 = tblA02.pk
        )
        LEFT JOIN 
     {your current query's subjoin}
        )

I don't mix those puppies unless I have a darn good reason.

Or, since there is presumably exactly one matching attribute record for each tblA record, you could also do this:

((tblA = tblA01 = tblA02) -> (tblAB = tblB))

Obviously, tblA, tblA01 and tblA02 would be inner joined on their appropriate keys, something like this:

SELECT (fields)
FROM
    (SELECT (fields) 
     FROM 
        TblA02 
        INNER JOIN 
         (TblA01 INNER JOIN tblA ON tblA.attr1 = tblA01.pk)
        ON tblA.attr2 = tblA02.pk
     ) AS QryA
LEFT JOIN 
     (your current query's subjoin)

[oemar00]

Hello,
Thank you for the response, sorry for the confusion the ON keyword was not missing, as I pasted the SQL from my editor to the site it combine two lines into one so you see the word qryA_AB_BON instead of qryA_AB_B ON I have fixed the error after you pointed that out.

I also try to understand the relational calculus notation that you used in answering the posting. I tried the first suggested solution (((tblA -> tblA01) -> tblA02) -> (tblAB = tblB)), as indicated in the SQL below but Access does not like the "FROM" before the "(((" saying that is "syntax error in FROM clause".

SELECT ID_A
, Attr01_A
, Attr02_A
, ID_B
, Attr01_B
, Attr02_B
, A01_Date
, A02_Date
FROM
(((tblA LEFT JOIN tblA01 ON tblA.Attr01_A = tblA01.A01_ID)
LEFT JOIN tblA02 ON tblA.Attr02_A = tblA02.A02_ID)
LEFT JOIN (tblAB INNER JOIN tblB ON tblAB.ID_B = tblB.ID_B));

I tried the second suggested solution

((tblA = tblA01 = tblA02) -> (tblAB = tblB)), as below but Access does not like the "FROM" before the "(SELECT" saying that is "syntax error in FROM clause".

SELECT ID_A
, Attr01_A
, Attr02_A
, ID_B
, Attr01_B
, Attr02_B
, A01_Date
, A02_Date
FROM (SELECT A02_Date FROM tblA02
INNER JOIN (tblA01 INNER JOIN tblA ON tblA.Attr01_A = tblA01.A01_ID)
ON tblA.Attr02_A = tblA02.A02_ID) AS qryA
LEFT JOIN (tblAB INNER JOIN tblB ON tblAB.ID_B = tblB.ID_B);

I think the solution is feasible however the bridge between the relational calculus notation and SQL syntax is not yet solved.

Thank you.

[Dal Jeanis]

try something like this:

SELECT 
   tA.ID_A, 
   tA.Attr01_A, 
   tA.Attr02_A, 
   tA.A01_Date, 
   tA.A02_Date, 
   tB.ID_B, 
   tB.Attr01_B, 
   tB.Attr02_B
FROM 
   (SELECT 
      tblA.ID_A, 
      tblA.Attr01_A, 
      tblA.Attr02_A, 
      tblA01.A01_Date, 
      tblA02.A02_Date 
   FROM 
      ((tblA 
       INNER JOIN 
       tblA01 
       ON tblA.Attr01_A = tblA01.A01_ID) 
      INNER JOIN tblA02 
      ON tblA.Attr02_A = tblA02.A02_ID)
   ) As tA
LEFT JOIN 
   (SELECT 
      tblAB.ID_A, 
      tblAB.ID_B, 
      tblB.Attr01_B, 
      tblB.Attr02_B
    FROM
      tblAB 
      INNER JOIN 
      tblB 
      ON tblAB.ID_B = tblB.ID_B) AS tB
ON tA.ID_A = tB.ID_A;

I verified that against a test version of your database. Aliasing the queries on each side of the "LEFT JOIN" keyword, and specifying precisely the fields you need in each query, including the ones needed to complete that LEFT JOIN, allows Jet to disambiguate the outer join.

Of course, you have to specify which of the two aliases you're getting ID_A from.

[oemar00]

try something like this:
I verified that against a test version of your database. Aliasing the queries on each side of the "LEFT JOIN" keyword, and specifying precisely the fields you need in each query, including the ones needed to complete that LEFT JOIN, allows Jet to disambiguate the outer join.

Of course, you have to specify which of the two aliases you're getting ID_A from.

Thank you this work.

When I ran this SQL, A4 from tblA did not show up (remember what you said in your first response "You want everything from tlbA,")but then after I changed both INNER JOIN into LEFT JOIN the resulting SQL re-run shows A4 while keep everything else intact, so that is an easy fix.

[Dal Jeanis]

Glad to help.