From: | Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
---|---|
To: | Christine Penner <chris(at)fp2(dot)ca> |
Cc: | Postgres-General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: query join issue |
Date: | 2010-09-16 22:03:12 |
Message-ID: | 34EDE053-40A0-4F9A-AE78-1A8B2229D74E@solfertje.student.utwente.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | hans | 2010-09-16 22:15:41 | Simple schema diff script in Perl |
Previous Message | Aram Fingal | 2010-09-16 21:26:14 | Re: Transposing rows and columns |