Very Computer

What is the correct syntax for the following.
I have table A joined to table B and C. I also have table A left outer
joined to tables D and E
and in turn D is joined to F

SELECT A.*,B.*,C.*,D.*,E.*,F.*
FROM A
          LEFT OUTER JOIN D
          ON A.colD      = D.colA
          LEFT OUTER JOIN E
           ON A.colE      = E.colA,
         B,
         C
WHERE A.col1 = ?
AND     A.colB = B.colA
AND     A.colC = C.colA

(I know I can move the B and C table joins and make them explicit joins
off A)

How do I include the join of table F to table D where F.colD = D.colF in
the case where 1) F is a LEFT OUTER and 2) where F is plain (INNER?)
join  

Thanks in advance

Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769

        (a left join (f left join d on somecondition) on somecondition)
or
        (a left join (f join d on somecondition) on somecondition)

However you need to be clear in your mind about the semantic behavior
you want before you can pick a join order, and your question certainly
didn't give enough detail for anyone to offer advice.  In either one of
the above examples, D rows that don't have a join partner in F will
disappear before they get to the A join, resulting in different results
than you had before --- that is, some A rows that were joined to D rows
would now be extended with with nulls.  If any of those rows make it to
the final output then you will see a different and probably less useful
answer.

The short form of my point is that outer joins aren't associative and so
the order in which you do them matters a lot.  The reason JOIN is
syntactically like an operator is so that you can control that ordering
through parentheses.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Tom,
Thanks for that.
I'll be testing the converted system thoroughly, so should pick up all
the anomalies that I've introduced!
I can now finish off some of the more obscure joins in the code before I
start the data import and then testing.

    > How do I include the join of table F to table D where F.colD = D.colF in
    > the case where 1) F is a LEFT OUTER and 2) where F is plain (INNER?)
    > join  

    I think you just want to parenthesize the join constructs:

        (a left join (f left join d on somecondition) on somecondition)
    or
        (a left join (f join d on somecondition) on somecondition)

    However you need to be clear in your mind about the semantic behavior
    you want before you can pick a join order, and your question certainly
    didn't give enough detail for anyone to offer advice.  In either one of
    the above examples, D rows that don't have a join partner in F will
    disappear before they get to the A join, resulting in different results
    than you had before --- that is, some A rows that were joined to D rows
    would now be extended with with nulls.  If any of those rows make it to
    the final output then you will see a different and probably less useful
    answer.

    The short form of my point is that outer joins aren't associative and so
    the order in which you do them matters a lot.  The reason JOIN is
    syntactically like an operator is so that you can control that ordering
    through parentheses.

                        regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

                   http://archives.postgresql.org

Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769