[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 776
  • Last Modified:

Access 2003 - SQL Statement Issues - Ambigious Outer joins.

I have the following query :

SELECT DISTINCT comCompanies.comCompany_Name, crpProducts.crpProduct_comCompanyID, crpProducts.crpProduct_Description, comCompanyDivisions.comCompanyDivision_comDivisionID, crpProducts.crpProduct_ID, comDivisions.comDivision_Code, TestDetail.ApplicationNum, TestDetail.TestTime, TestDetail.TestResult, TestDetail.TestType, TestDetail.TestPurpose, TestAgreements.AMCA210UnitType
FROM (((((comCompanies LEFT JOIN crpProducts ON comCompanies.comCompany_ID = crpProducts.crpProduct_comCompanyID) INNER JOIN comCompanyDivisions ON comCompanies.comCompany_ID = comCompanyDivisions.comCompanyDivision_comCompanyID) INNER JOIN comDivisions ON comCompanyDivisions.comCompanyDivision_comDivisionID = comDivisions.comDivision_ID) INNER JOIN crpCheckStatus ON crpProducts.crpProduct_ID = crpCheckStatus.ckstatusProductId) INNER JOIN crpCheckTestData ON crpCheckStatus.ckstatusId = crpCheckTestData.ckdataCkstatusId) INNER JOIN (TestDetail INNER JOIN TestAgreements ON TestDetail.ApplicationNum = TestAgreements.ApplicationNum) ON crpCheckTestData.ckdataTestType = TestDetail.TestType;


I am getting the following error for the above query:

"The SQL Statement could not be executed because it contains ambigious outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then unclude that query in your sql statement."

So then what I did was I devided the queries up into two and here is what I have.

Query one:
SELECT DISTINCT comCompanies.comCompany_Name, crpProducts.crpProduct_comCompanyID, crpProducts.crpProduct_Description, comCompanyDivisions.comCompanyDivision_comDivisionID, crpProducts.crpProduct_ID, comDivisions.comDivision_Code
FROM ((comCompanies LEFT JOIN crpProducts ON comCompanies.comCompany_ID = crpProducts.crpProduct_comCompanyID) INNER JOIN comCompanyDivisions ON comCompanies.comCompany_ID = comCompanyDivisions.comCompanyDivision_comCompanyID) INNER JOIN comDivisions ON comCompanyDivisions.comCompanyDivision_comDivisionID = comDivisions.comDivision_ID;

And the second query:
SELECT DISTINCT crpProducts.crpProduct_ID, crpProducts.crpProduct_Description, comCompanies.comCompany_Name, TestDetail.TestResult, TestDetail.TestType, TestDetail.TestPurpose, TestDetail.TestTime, TestDetail.ApplicationNum, TestAgreements.AMCA210UnitType, comCompanies.comCompany_ID
FROM ((crpProducts LEFT JOIN comCompanies ON crpProducts.crpProduct_comCompanyID = comCompanies.comCompany_ID) INNER JOIN crpCheckStatus ON crpProducts.crpProduct_ID = crpCheckStatus.ckstatusProductId) INNER JOIN (crpCheckTestData INNER JOIN (TestAgreements INNER JOIN TestDetail ON TestAgreements.ApplicationNum = TestDetail.ApplicationNum) ON crpCheckTestData.ckdataTestType = TestDetail.TestType) ON crpCheckStatus.ckstatusId = crpCheckTestData.ckdataCkstatusId
ORDER BY TestDetail.TestResult, TestDetail.TestPurpose;

The only thing with this is that when i create a report for the first query and then a sub report with the second query and create a relationship child and master node join on the crpProduct_ID when I run the report it is like its on an infinte loop it keeps trying to run the queries but getting no where. I have to crash out of it all the time.

Has anyone got any ideas. The crpProduct_ID is the same all around it is a unique identifier, so I don't see why there is an issue here. Any suggestions on getting around this issue? Thanks again in advance for all the help.

0
sabrina_spillane
Asked:
sabrina_spillane
2 Solutions
 
tcullerCommented:
In my experience, Access does not allow the mixing of Join types.  In other words, all of the joins in a specific SQL statement must be LEFT, or they must all be INNER.  Even in your attempt to split your operation into two SQL statements, each of the SQL statements still has a mix of LEFT and INNER joins.  That is what is causing the ambiguous outer join error.

Tim
0
 
sabrina_spillaneAuthor Commented:
Hi Tim,
So could I put the coditions into a where clause or just have to keep make queries until all the joins are either Left or Inner. Do all my queries then for the one report have to be eiher left or inner or can i have one query that is all left and another query that is inner. Any ideas on getting around this.
0
 
Jeffrey CoachmanCommented:
sabrina_spillane,

Here is the microsoft link on "Ambiguous Outer Joins"

Also, as you requested, it does in fact list all of the "Legal" and "Illegal" Join combinations

For example:
   OK : Consultants ---- Active Consultants ---- Projects
   OK : Consultants ---- Active Consultants ---> Projects
   ERR: Consultants ---- Active Consultants <--- Projects

   ERR: Consultants ---> Active Consultants ---- Projects
   OK : Consultants ---> Active Consultants ---> Projects
   ERR: Consultants ---> Active Consultants <--- Projects

   OK : Consultants <--- Active Consultants ---- Projects
   OK : Consultants <--- Active Consultants ---> Projects
   OK : Consultants <--- Active Consultants <--- Projects

Hope this helps as well
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now