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.
-----Original Message-----
From: jake williamson [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 11, 2002 6:25 AM
To: ActiveServerPages
Subject: Re: multiple inner join revisited
hi tore and pete,
thanks for getting back to me. the 'leg' column wont actually be
available to the user to search so we should be ok on that one - it's
purely for info purposes.
however, i'm getting an error:
syntax error (missing operator) in query expression 'CO.ID =..... (rest
of sql follows).
the version sent in by pete works but when i try it out it asks for 7
parameters - is this right?
i originally had set this up to try and reduce the amount of duplicate
tables and data. would i be better of taking the approach of having
having a 'origin', 'leg' and 'destination tables? it would make my life
easier but i was trying to reduce the clutter.
thanks again for help on this, really doing my head in!
cheers,
jake
On Thursday, October 10, 2002, at 04:55 PM, Bostrup, Tore wrote:
> What you need is:
>
> 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 CO.ID = J.Origin
> INNER JOIN Citys as CL
> ON CL.ID = J.Leg
> INNER JOIN Citys as CD
> ON CD.ID = J.Destination;
---
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]
