Need help with SQL JOIN's

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
I should be able to have both INNER JOIN's, but ACCESS won't seem to allow
it. Any advice?
Click to expand...

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]
 
John said:
Access is very picky about parentheses.

get a flavor of Access' particular dialect
of SQL.
Click to expand...

Does MS Access have a dialect of SQL? I think you meant to say 'Jet'.
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
Click to expand...

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
Click to expand...

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.

--
 
Back
Top