CODEWITHSUNDEEP
sql-serverms-access
user4242750
user4242750

Reputation: 197

Multiple joins in access, asking for parameter value

With the help of a prior article I was able to come up with the following solution for a multi table join for my example of three basic tables I want to join, however I am getting the parameter issue for PRIMARYKEY.

select *
FROM       (((
SELECT [PRIMARYKEY] FROM [TABLE 1] 
UNION
SELECT [PRIMARYKEY] FROM [TABLE 2]
UNION
SELECT [PRIMARYKEY] FROM [TABLE 3] )                                                                AS           A
LEFT JOIN  [TABLE 1]      ON    A.[PRIMARYKEY]    =     [TABLE 1].[PRIMARYKEY]               )
LEFT JOIN [TABLE 2]      ON     A.[PRIMARYKEY]     =    [TABLE 2].[PRIMARYKEY]                )
LEFT JOIN [TABLE 3]      ON     A.[PRIMARYKEY]     =    [TABLE 3].[PRIMARYKEY]

My idea is to get a full distinct listing of primary keys and then left join to the other tables, as I am aware there are no full joins in access.

Does anyone see something wrong with my script? Is it the "AS A" part that is throwing everything off?

Upvotes: 0

Views: 95

Answers (1)

HarveyFrench
HarveyFrench

Reputation: 4578

Please try this which uses aliases:

   SELECT *
     FROM ((( SELECT [PRIMARYKEY] AS MyField
                FROM [TABLE 1]
              UNION
              SELECT [PRIMARYKEY] AS MyField
                FROM [TABLE 2]
              UNION
              SELECT [PRIMARYKEY] AS MyField
                FROM [TABLE 3]
            ) AS A
            LEFT JOIN [TABLE 1]
                   ON A.[MyField] = [TABLE 1].[PRIMARYKEY] 
           )
           LEFT JOIN [TABLE 2]
                  ON A.[MyField] = [TABLE 2].[PRIMARYKEY] 
         )  
         LEFT JOIN [TABLE 3]
                ON A.[MyField] = [TABLE 3].[PRIMARYKEY]

You can do Cartesian joins in access (which is how I know full joins). I often use a one row table called dual to always return one row... (like in oracle).

Upvotes: 1

Related Questions