JOIN physical location vs ON location


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: JOIN physical location vs ON location

  1. #1
    Join Date
    Jan 2006
    Posts
    9

    JOIN physical location vs ON location

    Why does the physical order of the JOIN... vs the ON.... matter? This first sql statment executes just fine, but the second SQL statement gives the errors:
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "s.stno" could not be bound.
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "g.student_number" could not be bound.

    I am working with Microsoft SQL 2005

    SELECT DISTINCT(s.sname)
    FROM Student s
    JOIN Grade_report g
    ON s.stno = g.student_number
    JOIN Section sec
    ON g.section_id = sec.section_id
    JOIN Course c
    ON sec.course_num = c.course_number
    AND c.course_name like 'ACC%'


    SELECT DISTINCT(s.sname)
    FROM Student s
    JOIN Grade_report g
    JOIN Section sec
    JOIN Course c
    ON s.stno = g.student_number
    ON g.section_id = sec.section_id
    ON sec.course_num = c.course_number
    AND c.course_name like 'ACC%'

    I am more familiar with the older WHERE clause style, and would likely most of the time use the format of the second select statement above, but it produces errors! So in general should the JOIN and the related ON clause stay physically close together. Or what tips or concepts should I have or know!

    Thank-you!
    Last edited by LuluDev; 02-12-2007 at 10:24 AM.

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    It matters because that's what the SQL parser expects to receive:

    SELECT field1, field2, field3
    FROM first_table
    [INNER] JOIN second_table
    ON first_table.keyfield = second_table.foreign_keyfield

    If you send SQL a JOIN without an ON immediately following it, it can't parse it and doesn't know how what to JOIN.

    The older WHERE clause style is fine, by the way. If you're more comfortable with it, why not use it?
    Last edited by Phil Weber; 02-12-2007 at 11:43 AM.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Jan 2006
    Posts
    9
    Thank-you for your help on the proper order. It seems that some book examples group all of the ONs together and all of the JOINs together in the physical order. But as you responded and as my example shows, I might have more success doing JOIN/ON, JOIN/ON etc.

    Why use the ANSI style(s)? I am refreshing my SQL skills and the two SQL 2005 books that I have all use the JOIN method. So on my end I am learn-mode and trying to get familiar with the ANSI style. It might be a good idea for me to know both methods because I might run into both methods in existing code. I am sure under pressure I will revert back to my comfortable 'WHERE' method!

    Also a book that I have suggest that using the ANSI style is the best approach. The book is called 'Professional SQL 2005 Programming' by Vieira and he says 'I highly recommend that you use the ANSI method since it has much better portability between systems and is also much more readable'.... The book goes on to say '... Microsoft has indicated that it may not continue to support the old syntax indefinitely. I find it very hard to believe, given the amount of legacy code out there, that Microsoft will dup the old syntax any time soon, but you never know'. The book goes on with other various reasons.

Similar Threads

  1. JDOM Classpath Help Required
    By kpandya in forum Java
    Replies: 5
    Last Post: 01-15-2006, 07:10 PM
  2. JDOM Errors & How to set the classpath
    By kpandya in forum Java
    Replies: 0
    Last Post: 01-14-2006, 07:01 PM
  3. Problem with stored procedure
    By yip in forum Database
    Replies: 1
    Last Post: 09-24-2002, 12:31 AM
  4. Replies: 0
    Last Post: 08-03-2002, 03:19 PM
  5. Web Folders physical location?
    By bstring in forum Web
    Replies: 0
    Last Post: 12-10-2000, 11:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center