Syntax Question for Multiple Joins

[GaryJ]

Hi folks,
I'm using Access 2003. I have a table that includes foreign keys to several
other tables. I'm trying to write a query that will pull data from the
primary and secondary tables. So I'm trying something along the lines of:

SELECT a.1, a.2, a.3, a.4, b.1, b.2, c.1, c.2
FROM
a INNER JOIN b ON a.1 = b.1
INNER JOIN c ON a.2 = c.1;

In this model, a contains FK's for b and c, but there's no relation between
b and c.

What is the proper syntax for this? Many thanks

 

[Amy Blankenship]

Hi folks,
I'm using Access 2003. I have a table that includes foreign keys to
several
other tables. I'm trying to write a query that will pull data from the
primary and secondary tables. So I'm trying something along the lines of:

SELECT a.1, a.2, a.3, a.4, b.1, b.2, c.1, c.2
FROM
a INNER JOIN b ON a.1 = b.1
INNER JOIN c ON a.2 = c.1;

In this model, a contains FK's for b and c, but there's no relation
between
b and c.

What is the proper syntax for this? Many thanks

I think the syntax is pretty close. Try something like

Select (your fields) FROM b INNER JOIN
(a INNER JOIN c ON a.2=c.1)
ON a.1=b.1

You could also consider just using the query builder. Open a new query,
show tables a, b, c. Drag a.1 to b.1. Drag a.2. to c.1. Double-click each
field you want to select.

HTH;

Amy

 

[GaryJ]

Amy,
Thanks very much for the help. Your solution worked. It looks like Access
requires you to build a "join chain" - i.e., as you suggested I select FROM b
INNER JOIN a(INNER JOIN c ON ...) ON ... What if I needed one more join --
say I had a table d that was related to a but not to b or c? Thanks again!

Gary

 

[John W. Vinson]

Amy,
Thanks very much for the help. Your solution worked. It looks like Access
requires you to build a "join chain" - i.e., as you suggested I select FROM b
INNER JOIN a(INNER JOIN c ON ...) ON ... What if I needed one more join --
say I had a table d that was related to a but not to b or c? Thanks again!

As Amy says, use the query grid. Access is very snarky about correct
parentheses - a multitable query must join each table to a parenthesis-nested
join of other tables. The order is significant.

You do have a major problem here though, I fear! If you have A joined to B, A
joined to C, and A joined to D, with no relationships or joins among B, C and
D, you will get a "Cartesian explosion". If there are 5 records in B, 10 in C
and 5 in D related to an individual record in A, you will get all 250 possible
combinations of records in the child tables... probably not what you want!

If you're just trying to *display* or edit the data, consider using a Form
based on A, with three Subforms based on B, C and D respectively. You can also
use a Report with Subreports to manage the combinatorial dilemma.

 

[GaryJ]

John,
Thanks for the clarification. I believe I'm going to stop fighting the
problem. My secondary tables B, C, D are essentially lookup tables. Table A
holds FKs for stuff like people's names, which are in B and status codes,
which are translated in C, etc. You were absolutely correct in your thinking
of how the query is going to be used -- I wanted to use it as a data source
for a form. So the suggestion about Forms/Subforms is also very much
appreciated. Cheers,

Gary

 

[Amy Blankenship]

As Amy says, use the query grid. Access is very snarky about correct
parentheses - a multitable query must join each table to a
parenthesis-nested
join of other tables. The order is significant.

You do have a major problem here though, I fear! If you have A joined to
B, A
joined to C, and A joined to D, with no relationships or joins among B, C
and
D, you will get a "Cartesian explosion". If there are 5 records in B, 10
in C
and 5 in D related to an individual record in A, you will get all 250
possible
combinations of records in the child tables... probably not what you want!

If you're just trying to *display* or edit the data, consider using a Form
based on A, with three Subforms based on B, C and D respectively. You can
also
use a Report with Subreports to manage the combinatorial dilemma.

I don't agree with this. For instance if D contained a set of descriptions
for a key that was used for brevity in A, then you wouldn't have any ill
effects at all. It also seems to me more likely in the case of an inner
join that joining to D would result in fewer, not more, records. The lack
of a matching record in D, for example, would cause the entire query to come
up empty.

I'm constantly amazed at the weird stuff the query builder allows me to get
away with. I often have a table or query sort of stuck on off to the side
to limit results or pull in additional information. If I have a table or
query that I know contains a single record, I sometimes just stick it in
there without joining it to anything.

You could also consider doing it like this
http://www.firstsql.com/tutor3.htm#join

 

[John W. Vinson]

I don't agree with this. For instance if D contained a set of descriptions
for a key that was used for brevity in A, then you wouldn't have any ill
effects at all. It also seems to me more likely in the case of an inner
join that joining to D would result in fewer, not more, records. The lack
of a matching record in D, for example, would cause the entire query to come
up empty.

You're right of course - it depends on the direction of the joins. If B, C and
D are lookup tables for fields in A, then the relationship is one (B) to many
(A), and there should be no problem with the multitable query; it will even
allow A to be updated. I was thinking that it was one A to multiple B (C and
D).