Query need more attributes from other tables

[oemar00]

Hello,


Refer to the attached file, I have five tables tblA, tblB, tblC, tblX, and tblY.
tblA related to tblB many-to-many through tblC, I found records in tblA with no related record in tblB by executing qryA_Missing_C in the attached file:

SELECT tblA.ID_A
          , tblA.AttrX_A
          , tblA.AttrY_A
          , tblA.Attr01_A
          , tblA.Attr02_A
          , tblA.Attr03_A
FROM tblA
WHERE ID_A 
             NOT IN 
               (SELECT ID_CA FROM tblC
                             WHERE ID_CA = ID_A)
ORDER BY 1;

I want to add attributes from tblX and tblY to the result by executing qryA_Missing_C02 resulting in no records found. There are records in tblX and tblY that matches the result query qryA_Missing_C but even if the record does not exist in either tblX or tblY it should still return NULL in the attributes. The syntax might got me on this one. Changing INNER JOIN into LEFT JOIN does not work, Access consider that as invalid statement. There is similarity with the one I post before however the difference is significant enough that I cannot use the same formula as before.

SELECT tblA.ID_A
          , tblA.AttrX_A
          , tblA.AttrY_A
          , tblA.Attr01_A
          , tblX.X_Date
          , tblY.Y_Date
          , tblC.ID_CB
          , tblC.ID_CA
FROM tblB 
INNER JOIN ((tblY INNER JOIN (tblX 
                              INNER JOIN tblA ON tblX.X_ID = tblA.AttrX_A) ON tblY.Y_ID = tblA.AttrY_A) 
                              INNER JOIN tblC ON tblA.ID_A = tblC.ID_CA) ON tblB.ID_B = tblC.ID_CB
WHERE (Not (tblC.ID_CA)=[tblA].[ID_A]);

Thank you again.

[Dal Jeanis]

Okay, I have no idea what you think that second SQL statement has to do with the first one. The first one requires that there be no matching record in tblC, but the second requires that tblB be INNER JOINED to the tblC record that doesn't exist in the first one. You can't get a reasonable result that isn't empty.

You can't get matching attributes off tblB based on a match with tblC when you want tblA records that are not in tblC.

Try something like this -

SELECT 
   tblA.ID_A,
   tblA.AttrX_A,
   tblX.X_Date,
   tblA.AttrY_A,
   tblY.Y_Date,
   tblA.Attr01_A,
   tblA.Attr02_A,
   tblA.Attr03_A
FROM 
   (((tblA 
      LEFT JOIN tblC ON tblC.ID_CA = tblA.ID_A)
     LEFT JOIN tblX ON tblX.X_ID = tblA.AttrX_A)
    LEFT JOIN tblY ON tblY.Y_ID = tblA.AttrY_A) 
WHERE tblC.ID_CA Is Null
ORDER BY tblA.ID_A;

[oemar00]

Dal Jeanis,
You are correct, this solution work. After you show the solution is seems easy but not at the time when I was looking for a solution.

Thank you.

[Dal Jeanis]

oemar - yep, I know how that feels. Lots of times I've tried method after method until I've forgotten what exactly my goal was.

In fact, every time I have to do an INNER JOIN and a LEFT JOIN in the same SQL, I have to google up which ways Access will allow it.

From http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

A LEFT JOIN or a RIGHT JOIN can be nested inside an INNER JOIN, but an INNER JOIN cannot be nested inside a LEFT JOIN or a RIGHT JOIN. See the discussion of nesting in the INNER JOIN topic to see how to nest joins within other joins.

If I can arrange it so that everything is LEFT JOINS, as I did here, then I know it should work.

Another useful strategy is found here - http://stackoverflow.com/questions/5...n-in-ms-access

If you need LEFT for the first join and INNER for the second, you can move the tables from the second join into a subquery.