Does it make any difference joining in a where clause instead of using
inner and outer?
For example:
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;
Vs.
SELECT J.ID, CO.City as Origin, CL.City As Leg, CD.City As Destination
FROM Journeys as J, Citys as Co, Citys as Cl, Citys as CD
WHERE CO.ID = J.Origin and CL.ID = J.Leg and CD.ID = J.Destination
Pete Lundrigan
-----Original Message-----
From: David L. Penton [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 10, 2002 12:39 PM
To: ActiveServerPages
Subject: SQL Stuff, Was: RE: multiple inner join revisited
I updated the join examples on my website a few weeks back to include
Self-joins, triple joins, and cross joins with descriptions and such.
Perhaps it can help people with examples and data.
http://www.davidpenton.com/testsite/joins/Default.aspx
David L. Penton, Microsoft MVP
JCPenney Application Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"
[EMAIL PROTECTED]
Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol
-----Original Message-----
From: Bostrup, Tore [mailto:[EMAIL PROTECTED]]
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]]
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]
