Crazy Join Statement

  • Thread starter Thread starter xcube
  • Start date Start date
X

xcube

Hello,

These are my tables ( very stripped down )

TABLE persons (personID , persons_name);

TABLE persons_car (persons_carID
, personID integer
, make_of_car
, year_of_car, manuID);

TABLE manufacturer (manuID
, manufacturer);

A person can have car(s) or none so I need to return the following data

persons_name, make_of_car, manufacturer

Example of results I need returned:
Joe Blow, Civic, Honda
Paul Smith,,, ( does not have any cars )
Frank Grimes,Escort,Ford
Waylon Smithers,, ( does not have any cars )

I'm doing the following but obviously it's wrong:

SELECT persons.persons_name, persons_car.make_of_car,
manufacturer.manufacturer,persons_car
FROM persons
LEFT JOIN persons_car
ON persons.personID = persons_car.personID
LEFT JOIN manufacturer
ON persons_car.manuID = manufacturer.manuID

I tried using the Desing wizard but it gives me this error:
The SQL statement could not be executed because it contains ambiguous outer
joins. To force one of the joins to be performed first, create a seperate
query that performs the first join
and then include that in your SQL statment.

Can someone please help me out?

Thanks a bunch.
 
xcube said:
Hello,

These are my tables ( very stripped down )

TABLE persons (personID , persons_name);

TABLE persons_car (persons_carID
, personID integer
, make_of_car
, year_of_car, manuID);

TABLE manufacturer (manuID
, manufacturer);

A person can have car(s) or none so I need to return the following data

persons_name, make_of_car, manufacturer

Example of results I need returned:
Joe Blow, Civic, Honda
Paul Smith,,, ( does not have any cars )
Frank Grimes,Escort,Ford
Waylon Smithers,, ( does not have any cars )

I'm doing the following but obviously it's wrong:

SELECT persons.persons_name, persons_car.make_of_car,
manufacturer.manufacturer,persons_car
FROM persons
LEFT JOIN persons_car
ON persons.personID = persons_car.personID
LEFT JOIN manufacturer
ON persons_car.manuID = manufacturer.manuID
Click to expand...

Try adding () where shown (NOTE: I didn't test this one.)

SELECT persons.persons_name
,persons_car.make_of_car
,manufacturer.manufacturer
,persons_car
FROM (persons
LEFT JOIN
persons_car
ON persons.personID = persons_car.personID)
LEFT JOIN
manufacturer
ON persons_car.manuID = manufacturer.manuID
 
I tried using the Desing wizard but it gives me this error:
The SQL statement could not be executed because it contains ambiguous outer
joins. To force one of the joins to be performed first, create a seperate
query that performs the first join
Click to expand...

Since, even though a person might have no cars, every car has a
manufacturer, you can use an INNER join for that part of the query,
resolving the ambiguity. Access is picky about parentheses so that may
be another part of the problem. Try:

SELECT persons.persons_name, persons_car.make_of_car,
manufacturer.manufacturer,persons_car
FROM persons
LEFT JOIN (persons_car INNER JOIN manufacturer
ON persons_car.manuID = manufacturer.manuID)
ON persons.personID = persons_car.personID;
 
Like this:

SELECT persons.persons_name, PC.make_of_car, PC.manufacturer
FROM persons
LEFT JOIN
(SELECT persons_car.personID, persons_car.make_of_car,
manufacturer.manufacturer
FROM persons_car
INNER JOIN manufacturer
ON persons_car.manuID = manufacturer.manuID) As PC
ON persons.personID = PC.personID


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
I just want to say thanks for all your help. My problem has been put to rest
thanks to all of you.
 
Back
Top