CODEWITHSUNDEEP
sqlms-access
rluth
rluth

Reputation: 41

How to LEFT JOIN 4 tables in SQL?

I have 4 tables - Controls, Risks, Processes & Regulations. They each have ID common instances of ID numbers. For example (ID1 exists across the 4 tables). The problem is that under each table, the number of instances of each ID varies (for ex, ID1 exists 5 times in Controls, 3 times in Risks, 0 in Processes & once in Regulations).

I need to LEFT JOIN all these tables so they are all joined by ID number

The code below works until Line 3, but when I add Line 4, it gives me a "Resultant table not allowed to have more than one AutoNumber field" error 


SELECT * 

FROM Controls

LEFT JOIN Processes ON Processes.TO_PRC_ID = Controls.TO_PRC_ID

LEFT JOIN Risks ON Risks.TO_PRC_ID = Controls.TO_PRC_ID

LEFT JOIN Regulations ON Regulations.TO_PRC_ID = Controls.TO_PRC_ID

Upvotes: 0

Views: 124

Answers (3)

Frogrammer-Analyst
Frogrammer-Analyst

Reputation: 119

There are two different problems here. One problem is getting the right syntax for joining four tables. The other problem is the error message "Resultant table not allowed to have more than one AutoNumber field".

I don't have a copy of the tables being joined, but I suspect that more than one of them has an AutoNumber field in it. This is a field that automatically generates a record number when a new record is added to a table. Because the left join includes all of the fields in all of the tables, it will eventually include two different AutoNumber fields. MS Access cannot cope with that situation, so it declares there to be an error.

The proper though difficult way to deal with removing an AutoNumber field from a join is to list all of the other fields instead. So, instead of

FROM CONTROLS

one would need to code

FROM (SELECT A, B, C, D, WHATEVER FROM CONTROLS)

to eliminate the problem field.

If the tables have many fields, this becomes tedious to code. One alternative is to copy a table into a temporary table, drop the AutoNumber field from the copy, and use the copy instead of the original in the join. Whether this is a good or bad idea depends on the circumstances, such as how large the tables are, how often this would need to be done, and whether there is a good way to clean up the temporary tables later.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

You have two or more table with the same column name so try use full qualified column name in select 

SELECT c.TO_PRC_ID, p.TO_PRC_ID, r1.TO_PRC_ID, r2.TO_PRC_ID
FROM Controls c 
LEFT JOIN Processes ON  p p.TO_PRC_ID = c.TO_PRC_ID
LEFT JOIN Risks r1 ON r1.TO_PRC_ID = c.TO_PRC_ID
LEFT JOIN Regulations r2 ON r2.TO_PRC_ID = c.TO_PRC_ID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

MS Access requires extra parentheses for multiple joins:

SELECT * 
FROM (Controls LEFT JOIN
      Processes_Risks
      ON Processes_Risks.TO_PRC_ID = Controls.TO_PRC_ID
     ) LEFT JOIN
     Issues
     ON Issues.TO_PRC_ID = Controls.TO_PRC_ID

And the process continues:

SELECT * 
FROM ((Controls LEFT JOIN
       Processes_Risks
       ON Processes_Risks.TO_PRC_ID = Controls.TO_PRC_ID
      ) LEFT JOIN
      Issues
      ON Issues.TO_PRC_ID = Controls.TO_PRC_ID
     ) LEFT JOIN
     Regulations
     ON . . .

Upvotes: 1

Related Questions