Need pointers on joins in queries.

[Douglas J. Steele]

You don't use AND in conjunction with JOIN. I would expect the query should
be something like:

mEnq = "SELECT * FROM Table 1 INNER JOIN (Table2 INNER JOIN Table3 ON
Table1.R_ID =
Table3.R_ID) ON Table1.R_ID = Table2.R_ID ORDER BY Table1.R_ID"

In general, you're best off building the query through Access's graphical
query builder, then looking at the SQL that's generated. (You can get to it
through the View menu)

 

[Neil Gould]

Hi all,

Trying to follow the Access help on joins to link 3 tables in an ASP
query. The tables are linked on the key field (e.g. R_ID), and work as
expected in forms on local machines. Yet, the below example results in a
"missing operator" error.

mEnq = "SELECT * FROM Table 1 (INNER JOIN Table2 ON Table1.R_ID =
Table2.R_ID AND INNER JOIN Table3 ON Table1.R_ID = Table3.R_ID) ORDER BY
Table1.R_ID"

I expected it to error out for other reasons. ;-)

Any help is greatly appreciated.

Neil

 

[Neil Gould]

Hi Douglas,

You don't use AND in conjunction with JOIN. I would expect the query
should be something like:

mEnq = "SELECT * FROM Table 1 INNER JOIN (Table2 INNER JOIN Table3 ON
Table1.R_ID =
Table3.R_ID) ON Table1.R_ID = Table2.R_ID ORDER BY Table1.R_ID"

Interesting. The placement of your parentheses is a little puzzling,
though. Can you explain the placement ahead of "Table2..."?

In general, you're best off building the query through Access's
graphical query builder, then looking at the SQL that's generated.
(You can get to it through the View menu)

I'll look into that, as well.

Thanks,

Neil

 

[Douglas J. Steele]

Hi Douglas,


Interesting. The placement of your parentheses is a little puzzling,
though. Can you explain the placement ahead of "Table2..."?

Sorry, I can't explain the vaguaries of the Jet SQL dialect. <g> In other
dialects, you'd use

SELECT *
FROM Table 1
INNER JOIN Table2 ON Table1.R_ID = Table2.R_ID
INNER JOIN Table3 ON Table1.R_ID = Table3.R_ID
ORDER BY Table1.R_ID

If I had to guess, I'd say that you've got (Table2 INNER JOIN Table3 ON
Table1.R_ID = Table3.R_ID) as a subquery that gets evaluated before the
other join gets done, but I'm not sure whether Rushmore (the optimization
technology that's built into Jet) isn't smart enough to look and see when it
makes more sense to do a different subquery first based on the number of
rows in each table.

 

[Douglas J. Steele]

Hi Douglas,


That is exactly what I would do in a more conforming SQL dialect. However
with Jet SQL, who knows? I'll play with this and see.

BTW - I did take your other suggestion and build the query in Access and
look at it. My, what a mess, and it didn't work in the ASP code either.

That doesn't make sense. If it runs in Access, it should run from ASP
(assuming you're using the correct connection string)

Did you get an error from ASP?

 

[Neil Gould]

Hi Douglas,

Sorry, I can't explain the vaguaries of the Jet SQL dialect. <g> In
other dialects, you'd use

SELECT *
FROM Table 1
INNER JOIN Table2 ON Table1.R_ID = Table2.R_ID
INNER JOIN Table3 ON Table1.R_ID = Table3.R_ID
ORDER BY Table1.R_ID

That is exactly what I would do in a more conforming SQL dialect. However
with Jet SQL, who knows? I'll play with this and see.

BTW - I did take your other suggestion and build the query in Access and
look at it. My, what a mess, and it didn't work in the ASP code either.

If I had to guess, I'd say that you've got (Table2 INNER JOIN Table3
ON Table1.R_ID = Table3.R_ID) as a subquery that gets evaluated
before the other join gets done, but I'm not sure whether Rushmore
(the optimization technology that's built into Jet) isn't smart
enough to look and see when it makes more sense to do a different
subquery first based on the number of rows in each table.

Well, if I can't get the INNER JOINs to work, I may have to resort to
extremely inefficient methods to get the job done.

Thanks, again.

Neil

 

[Neil Gould]

That doesn't make sense. If it runs in Access, it should run from ASP
(assuming you're using the correct connection string)

Did you get an error from ASP?

The error was the same "missing operator" one as before. The above fails
in the same way (not too surprising, as it is "legitimage" SQL). ;-)

I've simplified the query to try tracking down the error. I can get a
single INNER JOIN to pass muster, but not the 3-table join. I'm pretty
sure it's a syntax problem, rather than a missing operator issue as
reported.

Best,

Neil