Search code examples
sqloracleansi-sql

Converting multiple oracle join to multiple ANSI join

Using this tool: http://107.170.101.241:8080/joinConverter/

I see that a simple Oracle join is converted from:

 SELECT *
     FROM Table1 t1,
          Table2 t2
    WHERE t1.id = t2.id

to ANSI join:

SELECT *
     FROM Table1 t1
inner join Table2 t2 on t1.id = t2.id

However, it errors out for more complex queries like this one:

SELECT *
 FROM Table1 t1,
      Table2 t2,
      Table3 t3
WHERE t2.id = t3.id
  AND t1.account_id = 5
  AND t2.coord in (t1.w, t1.x, t1.y, t1.z)
  AND t3.num IS NOT NULL

I get the error:

Error 1, Message: This table has no join condition: Table2
Error 2, Message: This table has no join condition: Table3

How would I fix this? What would be the appropriate ANSI syntax to use?

Solution

  • The syntax is:

    SELECT *
FROM Table1 t1 JOIN
     Table2 t2
     ON t2.coord IN (t1.w, t1.x, t1.y, t1.z) JOIN
     Table3 t3
     ON t2.id = t3.id
WHERE t1.account_id = 5
      t3.num IS NOT NULL