Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Unanswered: ANSI SQL Join syntax - multiple tables

    I'm used to using the old-style JOIN syntax and performing the joins in the WHERE clause. I'm trying to figure out the ANSI syntax though for doing them in the FROM clause, and am curious if someone can help me out.

    Here's the query I want:

    SELECT *
    FROM tableA A , tableB B , tableC C
    WHERE A.col1 = B.col1
    AND B.col2 = C.col2

    Here is my converted ANSI (SQL-92) syntax:
    SELECT *
    FROM (tableA A JOIN tableB B ON A.col1 = B.col1 ) JOIN tableC C ON B.col2 = C.col2

    Question:
    Is my ANSI version correct (equivalent to the first query)?
    Is that the only way to do it? I don't really like having to string multiple joins out with parenthesis like that.

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sure...I would say INNER JOIN though (no that it matters)

    You do know about outer joins though...right?

    Code:
    SELECT *
      FROM tableA A 
      JOIN tableB B 
        ON A.col1 = B.col1
      JOIN tableC C 
        ON B.col2 = C.col2
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the parentheses are definitely necessary in microsoft access, and nowhere else that i know of

    i'm not sure if the parentheses will be ignored in sql server (this is the sql server forum, after all), because i've never coded them in sql server, because i know they aren't necessary

    so if in doubt, leave them out, just remember to code them back in if you use microsoft access


    rudy
    http://r937.com/

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by r937
    the parentheses are definitely necessary in microsoft access, and nowhere else that i know of

    i'm not sure if the parentheses will be ignored in sql server (this is the sql server forum, after all), because i've never coded them in sql server, because i know they aren't necessary

    so if in doubt, leave them out, just remember to code them back in if you use microsoft access


    rudy
    http://r937.com/
    Since when?

    I always take them out in Access...same rules apply..esp when you duild your own sql in access...never use the qbe grid...(well maybe to select the columns...after that, it's painful...plus you can't union unless you go to the sql window...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    since when? since access 97

    i dunno about later versions, perhaps the parentheses on 3+ table joins are no longer required, but they definitely were in access 97, and i ain't upgrading because i see no reason to


    rudy

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm gonna check it out right now...didn't think so..

    We're talking like SELECT a INNER join B on a.id = b.id inner join c on b.id = c.id...

    right?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, three tables or more, access 97 requires that you parenthesize them two at a time

    select foo, bar
    from (
    a inner join b on a.id = b.id
    )
    inner join c on b.id = c.id


    rudy

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yup spent waaaay to much time with 2k...I remeber in 6.5 I think it was that way...


    but for joins only...not in the predicates...

    AND, Access still throws in waaaaaaaay too many parens....


    You can get away with

    SELECT *
    FROM ((Table1 a
    INNER JOIN table2 b
    ON a.id = b.id)
    INNER JOIN table3 c
    ON b.id = c.id)


    my bad....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •