query join issue

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!