Depends on the database engine as to whether the query plan generated is different. With SQL Server, care is taken so that the query plans are equivalent between ANSI-86 joins (joins in the WHERE clause) and ANSI-92 joins. But, don't expect that in future versions it will be like that. There are benefits to ANSI-92 joins, like prefiltering (in the ON clause) which can improve the query plan as well (this is only in certain situations).
David L. Penton, Microsoft MVP JCPenney Technical 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: Pete Lundrigan [mailto:[EMAIL PROTECTED]] 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 PROTECTED]
