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;
I suspect you have a design restriction in your database:
The "Leg" in your Journeys table allows only one stop, whether it represents
a stop on a direct, but not non-stop flight, or whether it represents a
plane change. This may or may not become an issue, and removing it may
require (an) additional table(s).
HTH,
Tore.
-----Original Message-----
From: jake williamson [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 10, 2002 10:02 AM
To: ActiveServerPages
Subject: multiple inner join revisited
hello,
got a tricky one but have a feeling that once i get it, it should open a
whole load of other ideas....
this is revisited (tore and sam may remember) so please excuse the
continue post:
i have:
table 'journeys' has three columns:
ID ORIGIN LEG DESTINATION
1 1 2 3
these three columns have look ups to a 'citys' table:
ID CITY
1 Scotland
2 London
3 Paris
when i use the access query wizard to build the sql statement it builds
this:
SELECT journeys. ID, journeys. ORIGIN, citys.CITY, journeys. LEG,
journeys.DESTINATION
FROM journeys INNER JOIN journeys ON citys. CITY = journeys.ORIGIN;
which gives me this result:
ID journeys. ID ORIGIN LEG DESTINATION
1 1 Scotland 2 3
how do i make the join give me:
ID journeys. ID ORIGIN LEG DESTINATION
1 1 Scotland London
Paris
it seems obvious but i just cant get it!!!
thanks for any help chucked at me,
cheers,
jake
---
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]
