#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    68
    Rep Power
    14

    how do i write this query in ANSI?


    ok. i am converting all my old non-ansi sql to sql code and ran across one that i didnt know how to convert in ansi....


    select * from table a, table b where a.column(+)=b.column

    converts to

    select * from table b left outer join a ON (a.column=b.column)

    cool..... but what do i do when i have a table that is the strong table on 1 outer join and then the weak table on another? i cant just declare it twice cuz i get table already defined...

    how would i convert......

    select * from table a, table b, table c
    where a.column(+)=b.column AND
    b.column(+)=c.column

    ?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,375
    Rep Power
    391
    Code:
    select * 
      from c left join b on c.c = b.c
      left join a on b.c = a.c
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    68
    Rep Power
    14
    fair enough... but i forgot to mention that tables A and table C
    do not have a common column...

    i didnt mean for it to appear that the "column" was the same column...

    let me rewrite the query to be...

    select * from table a, table b, table c
    where a.column(+)=b.column AND
    b.column2(+)=c.column2
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,375
    Rep Power
    391
    Why would that matter really?

    Code:
    select * 
      from c left join b on c.c1 = b.c1
      left join a on b.c2 = a.c2
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    68
    Rep Power
    14
    i guess that looked to me like i would be LEFT JOINING C to A when there was no relation

    i guess thats right.

IMN logo majestic logo threadwatch logo seochat tools logo