Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    1

    Question Unanswered: ANSI syntax for joining 4 tables-am I correct?

    Hi, I'm new to these forums and I've searched everywhere and can only find syntax and examples on how to join 2 and 3 tables using ANSI, but I need to join 4. For a traditional join it would be:

    SELECT a.one, a.two, b.one, b.three, b.four, d.seven
    FROM tablea a, tableb b, tablec c, tabled d
    WHERE a.one = b.one
    AND a.two = c.two
    AND c.six = d.six;

    Here are the tables:

    _tableb_
    one
    three
    four

    _tablea_
    one
    two

    _tablec_
    two
    six

    _tabled_
    six
    seven
    eight

    Does anyone know how to do this using an ANSI join or have any resources that can point me in the right direction. I know if I wanted to join three tables it would be:

    SELECT a.one, a.two, b.one, b.three, b.four, c.six
    FROM tableb b JOIN tablea a ON b.one = a.one JOIN tablec c ON a.two = c.two;

    so would joining a fourth be:

    SELECT a.one, a.two, b.one, b.three, b.four, d.seven
    FROM tableb b JOIN tablea a ON b.one = a.one JOIN tablec c ON a.two = c.two JOIN tabled d ON c.six = d.six; ???

    Is the above correct or is there another way to do this using an ANSI Join?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andierosebud
    Is the above correct ...
    what happpened when you tested it?

    Quote Originally Posted by andierosebud
    ... or is there another way to do this using an ANSI Join?
    actually, the old style of joins is also compatible with ANSI SQL --

    SELECT a.one, a.two, b.one, b.three, b.four, d.seven
    FROM tablea a, tableb b, tablec c, tabled d
    WHERE a.one = b.one
    AND a.two = c.two
    AND c.six = d.six;

    however, most people agree that using JOIN syntax is better
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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