No, the general principle applies - when you have multiple "references" between two tables, you must join each reference to a separately aliased "copy" of the referenced table. What is specific to MS Access, is that it requires complex joins to be parenthesized in order to avoid syntax errors. SQL Server handles a wider variety of join syntaxes. Other databases may or may not support different types of join syntaxes. It's a bear sometimes.
To better understand parenthesized join expressions, think of each parenthesized join expression almost as if it were a table, in simplified pseudo syntax: ((A Join B On A.x = B.x) Join C on C.y = [A|B].y) Join D on D.z = [A|B|C].z) Starting with the inner parenthesis, we have a simple join between two tables A and B. Next, the result of that join is joined with table C, etc. If ALL the joins are INNER joins, the expression would logically be equivalent without the parentheses. The above is a relatively simple multiple join statement. With more complex joins, you will typically have to move the ON clauses when you introduce parentheses. I find it most helpful to use one or two letter table aliases and using CRLF and indentations in the statement to indicate its logical structure. HTH, Tore. ----- Original Message ----- From: "jake williamson" <[EMAIL PROTECTED]> To: "ActiveServerPages" <[EMAIL PROTECTED]> Sent: Thursday, October 17, 2002 11:21 AM Subject: Re: multiple inner join revisited > > > tore, > > that is the money!! i'm gonna spend some time to take this apart and > come back to you if thats cool. > > is this kind of thing specific to ms access? i've been trying to find > reading material to help me out with sql queries in access but i'm > finding it tricky. it's that age old problem of 3/4 of the book centers > around how to launch the application and make 'your first database' and > then the remaining 1/4 is the real stuff! the hunt is on... > > thank you again for all the help, > > back soon, > > jake > > > > On Friday, October 11, 2002, at 03:57 pm, Bostrup, Tore wrote: > > > I guess you are using MS Access - and it doesn't like the joins to be > > "equal". Some parenthesizing of the JOINS should resolve it: > > > > SELECT J.ID, CO.City as Origin, CL.City as Leg, CD.City as Destination > > FROM ( > > (Journeys AS J INNER JOIN Citys AS CO ON J.Origin = CO.ID) > > INNER JOIN Citys AS CL ON J.Leg = CL.ID) > > INNER JOIN Citys AS CD ON J.Destination = CD.ID; > > > > To create this query in the Access query designer, add the table > > Journeys > > once, and the table Citys three times. > > Delete the automatically create join between the two tables' ID fields, > > and > > drag Origin to ID in Citys, Leg to Citys_1, and Destination to Citys_2 > > (using the MS access default aliases). > > > > HTH, > > Tore > > > --- > You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] > To unsubscribe send a blank email to %%email.unsub%% > --- You are currently subscribed to activeserverpages as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED]
