Multiple LEFT OUTER/INNER JOIN on multiple tables

Question

I would like to convert the following Oracle SQL query syntax (Use LEFT OUTER JOIN instead of (+)):

SELECT  *
FROM  TABLEA A,     TABLEC C,       TABLEE E,
            TABLEF F,   TABLEB B,   TABLEG G,   TABLEH H, 
            TABLEX XY,  TABLED D,   TABLEI I
WHERE XY.MYCOL = A.MYCOL

  AND B.X = A.X
  AND D.Y = A.Y
  AND D.Z = A.Z

 AND D.COL1 = C.COL1

  AND E.COL2 = C.COL2
  AND E.COL3 = C.COL3
  AND E.COL4 = C.COL4

  AND E.COL5 = D.COL5
  AND E.COL6 = D.COL6
  AND E.COL7 = D.COL7

  AND E.COL8 = F.COL8

  AND G.COL9 = D.COL9

  AND H.COL10(+) = E.COL10
  AND H.COL11(+) = E.COL11
  AND H.COL12(+) = E.COL12
  AND H.COL13(+) = E.COL13

  AND G.lid = 1

  AND I.COL14 = C.COL14
  AND I.COL15 = C.COL15
  AND I.COL16 = C.COL16
  AND I.COL17 = 'VAL'

Here is what I tried so far:

SELECT *
FROM TABLEA A JOIN TABLEB B ON B.X = A.X
JOIN TABLEXY XY ON XY.MYCOL = A.MYCOL
JOIN TABLEC C ON C ON C.Y = A.Y
JOIN TABLEC C ON C.Z = A.Z
JOIN TABLED D ON D.COL1 = C.COL1
JOIN TABLEE E ON E.COL2 = C.COL2
JOIN TABLEE E ON E.COL3 = C.COL3
JOIN TABLEE E ON E.COL4 = C.COL4

JOIN TABLEE E ON E.COL5 = D.COL5
JOIN TABLEE E ON E.COL6 = D.COL6
JOIN TABLEE E ON E.COL7 = D.COL7

JOIN TABLEF F ON E.COL8 = F.COL8
JOIN TABLEG G ON G.COL9 = C.COL9

LEFT OUTER JOIN TABLEH H ON H.COL10 = E.COL10
LEFT OUTER JOIN TABLEH H ON H.COL11 = E.COL11
LEFT OUTER JOIN TABLEH H ON H.COL12 = E.COL12
LEFT OUTER JOIN TABLEH H ON H.COL13 = E.COL13

AND F.lid = 1

JOIN TABLEI I ON I.COL14 = C.COL14
JOIN TABLEI I ON I.COL15 = C.COL15
JOIN TABLEI I ON I.COL16 = C.COL16
JOIN TABLEI I ON I.COL17 = 'VAL';

But I get the following error:

ORA-00918: définition de colonne ambigu
00918. 00000 -  "column ambiguously defined"
*Cause:    
*Action:

I would also like to understand the general methodology to translate such queries because I really don't understand it.

Answer 1

You don't need to repeat the JOIN <table> <alias> ON for each predicate.

SELECT *
FROM
     TABLEA A

JOIN TABLEB B ON B.X = A.X

JOIN TABLEXY XY ON XY.MYCOL = A.MYCOL

JOIN TABLEC C ON  C.Y = A.Y
              AND C.Z = A.Z

JOIN TABLED D ON D.COL1 = C.COL1

JOIN TABLEE E ON  E.COL2 = C.COL2
              AND E.COL3 = C.COL3
              AND E.COL4 = C.COL4
              AND E.COL5 = D.COL5
              AND E.COL6 = D.COL6
              AND E.COL7 = D.COL7

JOIN TABLEF F ON E.COL8 = F.COL8

JOIN TABLEG G ON G.COL9 = C.COL9

LEFT OUTER JOIN TABLEH H ON  H.COL10 = E.COL10
                         AND H.COL11 = E.COL11
                         AND H.COL12 = E.COL12
                         AND H.COL13 = E.COL13
                         AND F.lid = 1

JOIN TABLEI I ON  I.COL14 = C.COL14
              AND I.COL15 = C.COL15
              AND I.COL16 = C.COL16
              AND I.COL17 = 'VAL';

You only repeat the JOIN keywords when you're joining a new table...

FROM
  <table>
<JOIN>
  <table>
    ON <predicate>
<JOIN>
  <table>
    ON <predicate>

The predicate can be as complex as you need it to be. In your case it's just <equality> AND <equality> AND <equality> AND .....

Answer 2

I believe you want something like

SELECT *
FROM TABLEA A JOIN TABLEB B ON B.X = A.X
JOIN TABLEXY XY ON XY.MYCOL = A.MYCOL
JOIN TABLEC C ON C ON C.Y = A.Y AND
   C.Z = A.Z
JOIN TABLED D ON D.COL1 = C.COL1
JOIN TABLEE E ON E.COL2 = C.COL2 AND
   E.COL3 = C.COL3 AND
   E.COL4 = C.COL4 AND
   E.COL5 = D.COL5 AND
   E.COL6 = D.COL6 AND
   E.COL7 = D.COL7
JOIN TABLEF F ON E.COL8 = F.COL8
JOIN TABLEG G ON G.COL9 = C.COL9
JOIN TABLEI I ON I.COL14 = C.COL14 AND 
  I.COL15 = C.COL15 AND 
  I.COL16 = C.COL16 AND
  I.COL17 = 'VAL'
LEFT OUTER JOIN TABLEH H ON H.COL10 = E.COL10 AND
  H.COL11 = E.COL11 AND   
  H.COL12 = E.COL12 AND
  H.COL13 = E.COL13 AND 
WHERE
  F.lid = 1