Need help with SQL JOIN's

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian Kitt - 19 Apr 2005 02:59 GMT

I have a database with several 'master' tables and numerous 'support' tables.
I'm trying to create a SQL SELECT statement that will select the 'master'
record and pull in all associated 'support' tables. For some reason, I can't
get ACCESS to allow me to have more than one JOIN per SELECT. I've tried my
SELECT in Microsoft SQL 2000, and it allows me to JOIN more than 1 support
table. ACCESS just keeps giving me a syntax error.

For example

SELECT employee.name, employee.statecode, state.statename, employee.jobcode,
job.description FROM employee INNER JOIN state ON state.statecode =
employee.statecode INNER JOIN job ON job.jobcode = employee.jobcode

I should be able to have both INNER JOIN's, but ACCESS won't seem to allow
it. Any advice?

Reply to this Message

John Vinson - 19 Apr 2005 07:37 GMT

>I should be able to have both INNER JOIN's, but ACCESS won't seem to allow
>it. Any advice?

Access is very picky about parentheses. Try

SELECT employee.name, employee.statecode, state.statename,
employee.jobcode,
job.description FROM (employee INNER JOIN state ON state.statecode =
employee.statecode) INNER JOIN job ON job.jobcode = employee.jobcode

It may be worthwhile "slumming" in the Query Grid for a bit, and going
back to the SQL window to get a flavor of Access' particular dialect
of SQL.

John W. Vinson[MVP]

Reply to this Message

Jamie Collins - 19 Apr 2005 09:14 GMT

> Access is very picky about parentheses.
>
> get a flavor of Access' particular dialect
> of SQL.

Does MS Access have a dialect of SQL? I think you meant to say 'Jet'.

> >I should be able to have both INNER JOIN's, but ACCESS won't seem to allow
> >it.
[quoted text clipped - 5 lines]
> job.description FROM (employee INNER JOIN state ON state.statecode =
> employee.statecode) INNER JOIN job ON job.jobcode = employee.jobcode

AFAIK the OP's SQL is entry level SQL-92 compliant and the JOINs would
be performed from left to right. The problem is, the Jet SQL dialect
falls short of entry level SQL-92 and requires the order of JOINs to be
explicitly specified using parentheses, as you have posted. It's
probably a good idea to take this explicit approach in any case so I
wouldn't consider Jet to be deficient in this respect (not that Jet
isn't deficient in the JOINs department e.g. non support for FULL OUTER
JOIN, not being able specify predicates in an OUTER JOIN, etc).

> It may be worthwhile "slumming" in the Query Grid for a bit

The Query Grid, which is part of MS Access, has the opposite problem:
it doesn't really 'understand' the Jet layer and so adds too many
unnecessary parentheses and brackets, just to be ultra-conservative,
making it hard to tell where they are actually required.

Jamie.

--

Reply to this Message