Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Outer Join with More than 2 Tables "Operator Missing"

Status
Not open for further replies.

WillAtlGuy

Programmer
Sep 3, 2003
2
US
I am trying to perform a summary query that joins 4 tables. Ordinarily I don't work with Access. With DB2, I would write something like this:

SELECT customer.customerNo,
customer.name,
Sum(customerContact.ordersPlaced) AS Orders,
Sum(productSales.qtySold) AS Sales,
Sum(productReturns.qtySold) AS Returns
FROM customer
LEFT OUTER JOIN customerContact ON customer.customerNo=customerContact.customerNo
LEFT OUTER JOIN productReturns ON customer.customerNo=productReturns.customerNo
LEFT OUTER JOIN productSales ON customer.customerNo=productSales.customerNo
GROUP BY customer.customerNo, customer.name

But Access doesn't like that syntax and gives me an error: "missing operator".

I have searched the forums and found that most people recommend adding parenthesis like the following:

SELECT customer.customerNo,
customer.name,
Sum(customerContact.ordersPlaced) AS Orders,
Sum(productSales.qtySold) AS Sales,
Sum(productReturns.qtySold) AS Returns
FROM ((customer
LEFT JOIN customerContact ON customer.customerNo=customerContact.customerNo)
LEFT JOIN productReturns ON customer.customerNo=productReturns.customerNo)
LEFT JOIN productSales ON customer.customerNo=productSales.customerNo
GROUP BY customer.customerNo, customer.name;

But this produces the wrong result. It looks like it is creating nested join where table2 is joined to table1, table3 to table2, and table4 to table3. What I want is tables2, 3 and 4 to each be joined to table1.

The queries here are from an example application I created to illustrate the problem.

Any help would be greatly appreciated!
 
Hi,
I don't have time to try this but you might want to....

SELECT a.customerNo,
a.name,
Sum(b.ordersPlaced) AS Orders,
Sum(d.qtySold) AS Sales,
Sum(c.qtySold) AS Returns
FROM
customer a, customerContact b, productReturns c, productSales d
WHERE
a.customerNo = b.customerNo
AND
a.customerNo = c.customerNo
AND
a.customerNo = d.customerNo
GROUP by a.customerNo, a.name

*******************************************
If this doesn't work then look at the 'HAVING'.
John

*********************
John Nyhart
*********************
 
I just notice that this would require that there were records for the customer in all four tables. This may not work ..... try 'HAVING' .... John

*********************
John Nyhart
*********************
 
As far as I know, you can't use HAVING to create a join condition. I'll give it a try, but the HAVING will probably be performed after the join and without a WHERE or ON clause, you get a cartesian join. So you will end up with every row of every table joined with every row of every other table. So 4 tables with 100 rows each becomes 100,000,000 rows.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top