Left outer join within a join in from clause in a view

[Former Member]

I have a view that contains a "left outer join" within a "join" in the "from" clause in a view; here is the SQL:

       JOIN s1_currency_lang
         ON s1_contract.currency_code = s1_currency_lang.currency_code
       JOIN s1_release
            LEFT OUTER JOIN gsr_release_load_balance
              ON s1_release.contract_nbr = gsr_release_load_balance.contract_nbr
             AND s1_release.release_nbr = gsr_release_load_balance.release_nbr
         ON s1_contract.contract_nbr = s1_release.contract_nbr

How do I delimit the inner "left outer Join" from the outer "join" The error I get is:

Could not execute statement.
Syntax error near 'ON' on line 230

Line 230 is this line: ON s1_contract.contract_nbr = s1_release.contract_nbr I tried wrapping the "left Outer Join" with parentheses but that did not work.

Thanks

[MarkCulp]

The ON clause between s1_contract and s1_release needs to be before the LEFT OUTER JOIN clause. E.g.

       JOIN s1_currency_lang
         ON s1_contract.currency_code = s1_currency_lang.currency_code
       JOIN s1_release
         ON s1_contract.contract_nbr = s1_release.contract_nbr
       LEFT OUTER JOIN gsr_release_load_balance
         ON s1_release.contract_nbr = gsr_release_load_balance.contract_nbr
        AND s1_release.release_nbr = gsr_release_load_balance.release_nbr

[Former Member]

Mark: I see what you're saying but the results of the "join" on s1_release" table are meant to be filtered by the "left outer join" clause; I don't think your solution will produce the correct results. I understand that your solution will make the view compile. BTW, how did you get my initial post to format as I had entered it? When I saw the initial post LFs and CRs were eliminated; I wanted them to be there for reradibility purposes.

[VolkerBarth]

BTW, how did you get my initial post to format as I had entered it? When I saw the initial post LFs and CRs were eliminated; I wanted them to be there for reradibility purposes.

You can put your code snippets between a pair of <pre> tags to have it displayed that way...

[Former Member]

Volker: this solution: (s1_release LEFT OUTER JOIN gsr_release_load_balance ON s1_release.contract_nbr = gsr_release_load_balance.contract_nbr AND s1_release.release_nbr = gsr_release_load_balance.release_nbr) ... did not work - it produced the same error. Murray

[VolkerBarth]

In addition to Mark's answer, you can also use parantheses to specify the JOIN order and/or to group joins, such as:

FROM A INNER JOIN B ON A.ID = B.ID INNER JOIN C ON B.ID = C.ID

FROM A INNER JOIN (B INNER JOIN C ON B.ID = C.ID) ON A.ID = B.ID

FROM A INNER JOIN (B LEFT JOIN C ON B.ID = C.ID) ON A.ID = B.ID

Note, for inner joins the join order does not make a difference for the result as inner joins are commutative and associative, for left outer joins it might make a difference.

I guess in your case parantheses around the following expression should work:

    ...
    (s1_release
            LEFT OUTER JOIN gsr_release_load_balance
              ON s1_release.contract_nbr = gsr_release_load_balance.contract_nbr
             AND s1_release.release_nbr = gsr_release_load_balance.release_nbr)
    ...