SQL question, multiple JOINS in one query

[Eraser]

I have the following (simplyfied) database structure:

Person
persnr
name
workarea

PersonExt
persnr
email

Workareas
code
region
unit
location

Locations
id
Address
Street
City

The bold text is the database name with the fieldnames under it. The Person table contains information about different persons. Person.persnr refers to PersonExt.persnr. The PersonExt contains additional information about each person.
Person.Workarea refers to Workareas.Code. Workareas contains information about where the people are working. The Workareas table also contains a reference (workareas.location) to Locations.id, so that address information can be tied to a certain work area.

Now what I want to do is get a persons information, extended information, workarea information and the address info from that workarea, preferably in one single query.

I tried the following:

SELECT *
FROM persons AS P, personsext AS PE, workareas AS W, Locations AS L
WHERE P.persnr=PE.persnr And
W.code=P.workarea And
L.id=W.location;

However, whenever there's no matching PE.persnr, W.code or L.id the record isn't shown. I don't want all this additional information to be required so joins are required. So I tried the following:

SELECT *
FROM persons AS P
LEFT JOIN personsext AS PE ON P.persnr=PE.persnr
LEFT JOIN workareas AS W ON P.workarea=W.code
LEFT JOIN locations AS L ON W.location=L.id

but I get a syntax error with that. So how do I do this?

[Dave]

SELECT * FROM Person
LEFT OUTER JOIN PersonExt ON PersonExt.persnr = Person.persnr
LEFT OUTER JOIN WorkAreas ON WorkAreas.code = Person.workarea
LEFT OUTER JOIN Locations ON Locations.id = WorkAreas.Location

[Eraser]

Thanks, but it gives me the same error I keep getting:

Syntax error (missing operator) in query expression 'PersonExt.persnr = Person.persnr
LEFT OUTER JOIN WorkAreas ON WorkAreas.code = Person.workarea
LEFT OUTER JOIN Locations ON Locations.id = WorkAreas.Location'

Is this because of Access being annoying or what?

[Dave]

oh.. access... you're on your own there. I don't know how much access actually supports table joins.

try creating a view in the access designer.

If all else fails, you can always download this for free http://msdn.microsoft.com/vstudio/express/sql/

[mjrpes]

Try adding some parenthesis in there to group these table joins. Remember that this is a syntax error, so it's at the point of parsing the statement; it isn't even tring to run it yet.

[mjrpes]

Access supports table joins very well; but with multiple joins like you're doing, they need to all be LEFT OUTER, like you have, and you do indeed need to use parentheses to group them.

Do what Dave says and create this query in access designer, that'd be easiest.

[Dave]

That's why you download the free SQL Express version and upsize your shit to bling bling

[Eraser]

I found the solution

SELECT persons.*, personsext.*, workareas.*, locastions.*
FROM ((persons LEFT JOIN personsext ON persons.persnr=personsext.persnr)
LEFT JOIN workareas ON persons.workarea=workareas.code)
LEFT JOIN locations ON workareas.location=locations.id;

[mjrpes]

That's why you download the free SQL Express version and upsize your shit to bling bling

Microsoft advertises Visual C# 2005 Express as 'fun'. Fuck yeah.