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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
