Need help,please,with LEFT OUTER JOIN

[tomer]

Hi
I have 4 tables
Customers,a,b,c.

and i am tring to get sum of the amount column for all table in a single
query


SELECT customers.code, customers.name, SUM(tablea.amount) AS tablea,
SUM(tableb.amount) AS tableb, SUM(tablec.amount) AS tablec
FROM customers LEFT OUTER JOIN
tablec ON customers.code = tablec.code LEFT OUTER JOIN
tableb ON customers.code = tableb.code LEFT OUTER JOIN
tablea ON customers.code = tablea.code
GROUP BY customers.code, customers.name


I get 'Syntax error (mising operator) in query expression 'customers.code =
tablec.code LEFT OUTER JOIN
tableb ON customers.code = tableb.code LEFT OUTER JOIN
tablea ON customers.code = tablea.code '

and the following is hilighted(between the [])

custom[ers].name


thanks in advance.

 

[Tom Ellison]

Dear Tomer:

I'll assume you're using Jet for the database. There are some
significant differences in the requirements for MSDE.

I'm going to add aliases for the tables as follows:

customers Cu
tablec C
tableb B
tablea A

this cuts down on the length of what must be read to understand the
query.

SELECT Cu.code, Cu.name, SUM(A.amount) AS tablea,
SUM(B.amount) AS tableb, SUM(C.amount) AS tablec
FROM ((customers CU
LEFT JOIN tablec C ON C.code = Cu.Code)
LEFT JOIN tableb B ON B.code = Cu.code)
LEFT JOIN tablec C ON C.code = Cu.code
GROUP BY Cu.code, Cu.name

Notes:

1. a "left outer join" is designated by LEFT JOIN
2. Jet requires parentheses around multiple joins. Don't worry much
about the way these associate.
3. I have reversed the order of the ON clauses - just my own habbit.
4. Notice the readability of the aliasing.
5. For MSDE you don't need the parentheses around the JOINs.

Please let me know if this works for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts