On 16 Sep 2010, at 18:23, Christine Penner wrote:
> There could be many training_course records for each of the other tables. I
> want to get all records from the Train_mod and Train_comp table even if there
> are no training course records available. This is the query I'm trying and I
> get nothing. The data I'm trying this on has no training_course records but
> does have records in the other tables. What am I doing wrong.
>
> SELECT *
> FROM TRAIN_MOD LEFT OUTER JOIN TRAINING_COURSE ON
> TRAIN_MOD.TRM_SEQ_NO=TRAINING_COURSE.TC_TRM_SEQ
> LEFT OUTER JOIN TRAIN_COMP ON TRAIN_MOD.TRM_TRC_SEQ=TRAIN_COMP.TRC_SEQ_NO
> where TC_PUB_ED IS TRUE OR TC_SEQ_NO IS NULL
Most likely TC_PUB_ED and/or TC_SEQ_NO in your WHERE clause are actually
missing rows from TRAIN_COMP. The IS NULL condition may be succeeding, but
TC_PUB_ED is most not TRUE but NULL in all those cases.
The solution is to put those conditions in your ON clause, like so:
LEFT OUTER JOIN TRAIN_COMP ON (
TRAIN_MOD.TRM_TRC_SEQ=TRAIN_COMP.TRC_SEQ_NO
AND (TC_PUB_ED IS TRUE OR TC_SEQ_NO IS NULL)
)
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4c92942d10252119096304!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
