Greetings,

I have the following join, and in this join I am getting all of the data except that which joins the pa table to the dao table. When I try to join it using a left join, i get all of the data except this table. When I use a right join, I get nothing at all. If I make this a seperate join to dal (rather than nested as it is) I have no connection/path to the dao table because of the way that the postgres optimizer works, and I have to use another alias, and I THINK that the alias was causing other problems, but I'm not certain at the moment. Can someone lead me to the the best way to tackle this problem?

The query currently looks like this:

SELECT
    -- a bunch of columns here
    dr.profile_id

FROM
    dr,
    da,
    dal
    LEFT JOIN (  doo
        LEFT JOIN ( dao
            LEFT JOIN pa
ON ( pa.dao_id = dao.dao_id )) -- the problem area is here. - - There are cases where there are no related doo values set but - - I'm not sure how to write this better
        ON ( doo.dao_id = dao.dao_id
            OR pa.dal_id = doo.dal_id ))
    ON ( dal.dal_id = doo.dal_id
        OR dal.dal_id = pa.dal_id )

WHERE dr.dr_id = doo.dr_id
    AND dr.dr_id = '1'
    AND da.da_id = dal.da_id

ORDER BY da.name



Thank you in advance

Ilene


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

Reply via email to