Left join pa on dal resulting in null

From: Ilene <angel(at)weatherangel(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Left join pa on dal resulting in null
Date: 2005-08-12 16:24:39
Message-ID: C28D4A1D-D10A-4AC9-AD4D-1C6BB70D2447@weatherangel.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Browse pgsql-sql by date

  From Date Subject
Next Message Nick Stone 2005-08-12 17:57:34 Re: SQL Newbie
Previous Message Sean Davis 2005-08-12 16:02:16 Re: SQL Newbie