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!
