Обсуждение: query join issue

I have a query that joins 3 tables, TRAIN_MOD,TRAIN_COMP and TRAINING_COURSE,

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

Christine Penner
Ingenious Software
250-352-9495
christine@ingenioussoftware.com

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_modand Train_comp table even if there are no training course records available. This is the query I'm trying and
Iget nothing. The data I'm trying this on has no training_course records but does have records in the other tables.
Whatam 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
conditionmay 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!