the sql statement could not be executed because it contains ambiguous outer joins

[michaelxhermes]

the sql statement could not be executed because it contains ambiguous outer joins



Please can some one help me sort out this query error message.

If you run the query on the attached DB file it will give this pop up error.

I am sure it is a very simple fix. I want to know what caused this so I can show my students how to fix it and how to prevent it again.

Many thanks

Michael

[JoeM]

I am unable to download internet files from my current location.
If you change your query to SQL View, you can copy and paste the SQL code here for us to see.

[CJ_London]

you cannot mix outer joins with Cartesian joins (Cartesian means no join), so remove the Out_tbl since you are not using it.

But you will then get another error about ambiguous joins, so remove the Home_tbl, because you are not using that either.

And for future reference outer joins need to go in the 'same direction' you have both pointing to the Hols_tbl

If you need queries where you have this situation, you need to build them up as individual queries to 'isolate' the outer or Cartesian joins

edit:
@JoeM - if you want to add to this the sql is

SELECT Cust_tbl.CustRef, Cust_tbl.Forename, Cust_tbl.Surname, Hols_tbl.Cost
FROM Out_tbl, Cust_tbl LEFT JOIN (Home_tbl LEFT JOIN Hols_tbl ON Home_tbl.FlightRef = Hols_tbl.Home) ON Cust_tbl.CustRef = Hols_tbl.CustRef;

[michaelxhermes]

Thanks for your advice

But it should be a simple solution.

I have done the same import and query myself with no problem - See the attached file Holidays New - and the query in here works fine (Query More than 1 Hol)

But the original has a mistake I cannot find. - The pupil has done something odd.. But what?

They both look the same - I cannot see any difference in keys, types etc.

Any further advice would be appreciated
Michael

[CJ_London]

I repeat my post above. Your query and your student's are completely different - yours does not have outer or Cartesian joins. Just look at the two layouts - how can you say they are the same?

Yours


Your students

[CJ_London]

incidentally, your query does not require J9Out and J9Home because you don't require them in the query either

[michaelxhermes]

This DB query does not work
SELECT Cust_tbl.CustRef, Cust_tbl.Forename, Cust_tbl.Surname, Hols_tbl.Cost
FROM Out_tbl, Cust_tbl LEFT JOIN (Home_tbl LEFT JOIN Hols_tbl ON Home_tbl.FlightRef = Hols_tbl.Home) ON Cust_tbl.CustRef = Hols_tbl.CustRef;




This DB query works
SELECT Count(J9Cust.CustRef) AS CountOfCustRef, J9Cust.Forename, J9Cust.Surname, Avg(J9Hols.Cost) AS Average
FROM J9Out INNER JOIN (J9Home INNER JOIN (J9Cust INNER JOIN J9Hols ON J9Cust.CustRef = J9Hols.CustRef) ON J9Home.FlightRef = J9Hols.Home) ON J9Out.FlightRef = J9Hols.Out
GROUP BY J9Cust.Forename, J9Cust.Surname
HAVING (((Count(J9Cust.CustRef))>1));

[CJ_London]

Why do you think the first one should, given the join structure is completely different (on the FROM line) and the explanation as to why it doesn't per post #3? As far as Access is concerned, the first query does not follow the syntax that Access uses.

So far you have asked the same question 3 times and not acknowledged any of the responses. So I think you are wasting my time.

Good luck with finding the solution