outer joins on multiple tables

[freddy]

Hi,

I need to perform an outer join on more than one table,
but can't work out the syntax, and wondered if you can
help...

Basically, I've got a central Users table, which stores
all types of users, and I've also got separate users
tables for each "type" (ie with the corresponding extra
columns that relate to that type).

The central users table will have a corresponding child
record in any of the underlying users-split-by-type table.

I need to query the central users table, along with the
corresponding data from all the relevant child users
tables. The relationship between the central users and
the split-by-types tables are 1-1 or 1-0.

Example:

USERS:
USR_KEY,USR_FIRST_NAME
Example records:
1,"Fred"
2,"Bert"

ADVERTISERS:
ADV_KEY,ADV_WEBSITE,ADV_USR_KEY
Example record:
1,"Fred.com",1

CUSTOMERS:
CUS_KEY,CUS_ORDERCOUNT,CUS_USR_KEY
1,10,2

I want to retrieve:
USR_KEY
USR_FIRST_NAME
ADV_KEY
ADV_WEBSITE
CUS_KEY
CUS_ORDERCOUNT

so that I get:
1,"Fred",1,"Fred.com",null,null
2,"Bert",null,null,1,10

It'd be something along the lines of:
SELECT
USR_KEY,
USR_FIRST_NAME,
ADV_KEY,
ADV_WEBSITE,
CUS_KEY,
CUS_ORDERCOUNT
FROM USERS AS USR
LEFT OUTER JOIN ADVERTISERS
AS ADV ON ADV.ADV_USR_KEY=USR.USR_KEY
LEFT OUTER JOIN CUSTOMERS
AS CUS ON CUS.CUS_USR_KEY=USR.USR_KEY

but that syntax is wrong.
Huge thanks to anyone that helps out with the syntax;
it'd be very much appreciated.

 

[Michel Walsh]

Hi,


Try adding ( ) :

SELECT
USR_KEY,
USR_FIRST_NAME,
ADV_KEY,
ADV_WEBSITE,
CUS_KEY,
CUS_ORDERCOUNT
FROM (USERS AS USR
LEFT OUTER JOIN ADVERTISERS
AS ADV ON ADV.ADV_USR_KEY=USR.USR_KEY )
LEFT OUTER JOIN CUSTOMERS
AS CUS ON CUS.CUS_USR_KEY=USR.USR_KEY



Hoping it may help,
Vanderghast, Access MVP