ansaurus

Question

Is it possible to do a 3 table join in MS-Access?

Answer 1

A:

I have never come across such a limitation. Why don't you try it.

Preet Sangha 2010-04-30 08:49:23

Answer 2

A:

Yes, it's possible:

Select *
From A, B, C
Where A.a = B.b
And A.c = C.c

or

Select *
From A, B, C
Where A.a = B.b
And B.c = C.c

Jonners 2010-04-30 09:04:01

Do not encourage the use of implied joins, very very very bad query technique

HLGEM 2010-04-30 13:52:06

How/why is this 'very very very bad'? I've been constructing queries this way for 20 years and never had a problem - perhaps you could explain your criticism in sufficient detail that we understand your point of view and consequently make our own informed decisions...?

Jonners 2010-04-30 14:28:47

Jet/ACE optimizes implicit joins identically to the equivalent explicit join, so there's often no penalty for using an implicit join. Note I said "often" -- I think explicit joins are preferable where possible, so only use implicit joins when I have to (or where it's less complicated).

David-W-Fenton 2010-04-30 21:23:19

I'm still curious. Aside from a syntactic preference (I can see an argument in favour of explicit join syntax for clarity) which is, in my experience, a personal preference issue, is there any RDBMS engine that optimises explicit joins more successfully than implicit joins, or offers some other advantage to their use? I'm not 'against' explicit joins, I'd just like to know why I'm a very very very bad person for posting an example using implicit joins. If I have been Doing It Wrong for two decades, for Xods' sake someone tell me and all of us why!

Jonners 2010-05-01 09:22:10

I think Jet/ACE optimizes explicit joins better in some cases. I think it's more likely to be correctly use indexes. There's lots that can go wrong in writing an implicit join. On the other hand, there are things that are very easy with a WHERE clause that are complicated in a JOIN statement. And with Jet/ACE, you've got all the issues with proper parens in the JOIN when you have more than two tables. If you're writing your SQL by hand, the implicit join will likely be easier. But I am no fan of writing SQL by hand.

David-W-Fenton 2010-05-02 00:38:17

Answer 3

+1 A:

I once had a problem when I tried

select
  x,
  y
from 
  A        inner join
  B on k=l inner join
  C on f=g

This didn't work. But it works with parantheses:

select
  x,
  y
from ( 
  A          inner join
  B on k=l ) inner join
  C on f=g

René Nyffenegger 2010-04-30 09:13:15

Jet/ACE SQL's JOIN syntax has always required parentheses for joins of more than two tables. If you do the joins in the Access QBE, you'll always get a correct result.

David-W-Fenton 2010-05-02 21:35:00

Answer 4

+2 A:

All the various types of multi-table joins that are available in other flavour of SQL are permitted in MS-Access/Jet. For example, here's a straight three-table hierarchical example (a bit more real-world than the other answers here):

SELECT
    x.FirstName,
    x.Surname,
    r.RegionName,
    c.CountryName
FROM
    (Customer x LEFT JOIN Region r
    ON r.ID=x.RegionID)
    LEFT JOIN Country c
    ON c.ID=r.CountryID

Or did you want to know how to do it using the Visual Designer in MS-Access?

hawbsl 2010-04-30 11:00:15

Answer 5

+1 A:

Access can do most types of joins (apart from a full outer) I wonder with your 3 table join if you are doing an ambiguous outer join? Have a look at this KB article for an explanation

support.microsoft.com/kb/124937

Kevin Ross 2010-04-30 13:36:38

ansaurus

tags:

views:

answers:

Is it possible to do a 3 table join in MS-Access?

related questions