Can a SELECT query join across three tables?

[Steve Perkins]

I have a situation with Access 2000 in which I need to relate three tables
together in order to retrieve the appropriate rows. The raw SQL looks
something like this:

SELECT table_1.foo, table_2.bar
FROM table_1, table_2, mapping_table
WHERE table1.id = mapping_table.id
AND table2.description = mapping_table.description
AND table1.company = table2.company;

So there are three tables that have to be joined together, all in one step.
I can't chain together nested INNER JOIN's, because it's not just a matter of
the first table joining the second and the second joining the third... the
first and third join on a column as well.

A WHERE clause stretching across more than two tables like this is fairly
trivial in full-sized relational databases, but Access treats it strangely.
Access handles the first WHERE criteria, but then prompts me for input on the
next two AND clauses. Access thinks those are parameters or something. Does
anyone know if what I'm doing is possible, or whether Access simply lacks the
ability to join across three tables in one step? (I don't have any control
over changing the table structure) Thanks in advance!

 

[Dorian]

The Syntax of the Select statement is described in Access Hekp screens.
The basic syntax for what you want is:
SELECT ... FROM ...
INNER JOIN ... ON ...
INNER JOIN ... ON ...
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Clifford Bass]

Hi Steve,

You appear to be using table_1 and table1; and table_2 and table2.
Correct that and it should work.

Clifford Bass

 

[Steve Perkins]

Dorian -

If I use back-to-back INNER JOIN's like that, how can I join directly from
the first table to the third? The syntax you're describing is great when
you're going from the first to the second, and then the second to the third,
but I don't think it provides a means to ALSO go directly from the first to
third as part of the same operation. Please fill me in if I'm wrong and
missing something.


Clifford -

Sorry, just a typo. In the actual application, the tables have more
meaningful names than "table_1" and "table_2", etc. I just abstract out the
names of tables and columns because companies get a little touchy about
broadcasting their actual schemas in public.

 

[Clifford Bass]

Hi Steve,

Prompting for parameters usually means something is misspelled. If you
are sure that is not the case, it could be trying to use description and
maybe company as potential reserved words. In which case you could try
adding square brackets around all of the items.

SELECT [table_1].[foo], [table_2].[bar]

And so forth.

Clifford Bass

 

[Dorian]

You could always use the old syntax:
SELECT ... FROM A, B, C
WHERE A. = B. AND B. = C. AND A.=C.;
I use this a lot and it works fine though not recommended by purists.

However, there is a way to use the new syntax with parentheses. Look in
Access Help, the syntax is explained there.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Rick Brandt]

Dorian -

If I use back-to-back INNER JOIN's like that, how can I join directly
from the first table to the third? The syntax you're describing is
great when you're going from the first to the second, and then the
second to the third, but I don't think it provides a means to ALSO go
directly from the first to third as part of the same operation. Please
fill me in if I'm wrong and missing something.

SELECT ....
FROM Table1 A
INNER JOIN Table2 B ON A.foo = B.foo
INNER JOIN Table3 C ON A.bar = C.bar

See how the second join is between the first and third table?

 

[Clifford Bass]

Hi Steve,

I don't think Access 2000 will allow that kind of triangular join;
probably complain. However, since I am unable to test that--if you want to
try it, here is the SQL from Access 2007, which does allow it:

SELECT table_1.foo, table_2.bar
FROM table_2 INNER JOIN (table_1 INNER JOIN mapping_table ON table_1.id =
mapping_table.id) ON (table_1.company = table_2.company) AND
(table_2.description = mapping_table.description);

Clifford Bass

 

[Bob Barrows]

I have a situation with Access 2000 in which I need to relate three
tables together in order to retrieve the appropriate rows. The raw
SQL looks something like this:

I don't understand the problem. Use the GUI designer to create your joins
and then switch to SQL View to see what it's supposed to look like - note:
with Jet, parentheses are required to nest joins which involve more than two
tables. The statement must resolve to: FROM <table expression> JOIN <table
expression> ON

The key is that a table expression can be a nested join between two table
expressions:

FROM (table1 as t1 join table2 as t2 ON t1.col1=t2.col1)
join table3 as t3 ON t3.col1=t1.col1 AND t2.col2=t2.col2

 

[Clifford Bass]

Hi Bob,

He is doing a triangle join, not a pair of regular joins as you
describe. Hopefully this will come through.

Table1.Field1 --> Table2.Field1
Table1.Field2 Table2.Field2
/\ |
| \/
| Table3.Field2
+------------ Table3.Field1

Now, I agree with what you wrote: just try it in the designer, see what
happens. Just tested it in Access 2003 and it works there also. But does it
in Access 2000? He was saying he was having troubles. I have a vague memory
that some earlier version of Access would complain about ambiguous joins if
you tried that. Which is not the problem he was reporting. The solution of
course was to do the first two joins and then use the where clause for the
third. Or, as he did, do it all in the where clause. Since he has not
reported back and since he could not post his actual SQL, we do not yet know
quite what the issue is/was.

Clifford Bass

 

[Bob Barrows]

Hi Bob,

He is doing a triangle join, not a pair of regular joins as you
describe. Hopefully this will come through.

Table1.Field1 --> Table2.Field1
Table1.Field2 Table2.Field2
/\ |
| \/
| Table3.Field2
+------------ Table3.Field1

Now, I agree with what you wrote: just try it in the designer,
see what happens. Just tested it in Access 2003 and it works there
also. But does it in Access 2000? He was saying he was having
troubles. I have a vague memory that some earlier version of Access
would complain about ambiguous joins if you tried that.

Only if inner and outer joins were mixed