Обсуждение: 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/09/2010 16:05, Christine Penner wrote: > 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 Can you show us the table schemas? Also, I think it's a good idea to qualify the columns in the WHERE clause, to prevent any possible ambiguity. Without seeing the table definitions I'm only guessing, but is it possible that these are doing something other than what you expect? Ray. PS - I personally find all-caps SQL very hard to read. :-) -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
Training Course tc_seq_no (primary key) tc_trm_seq (foreign key to train_mod table) ... and some other title, desc etc fields train_mod trm_seq_no (primary key) trm_trc_seq (foreign key to train_comp table) .. and title etc fields train_comp trc_seq_no (primary key) .. and title etc fields We don't qualify the field names in the where because there is really no chance we have duplicate field names. This is because of the software we use to access Postgres. Thats also the reason for the all caps. all of the query up to the where is put together for me based on info I set up for the query. Hope this info helps. I'm sure there is something wrong with the join or something, I just don't see it. Christine At 10:22 AM 16/09/2010, you wrote: >On 16/09/2010 16:05, Christine Penner wrote: >>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 > >Can you show us the table schemas? > >Also, I think it's a good idea to qualify the columns in the WHERE >clause, to prevent any possible ambiguity. Without seeing the table >definitions I'm only guessing, but is it possible that these are >doing something other than what you expect? > >Ray. > >PS - I personally find all-caps SQL very hard to read. :-) > >-- >Raymond O'Donnell :: Galway :: Ireland >rod@iol.ie > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general
On 16/09/2010 18:33, Christine Penner wrote: > Training Course > tc_seq_no (primary key) > tc_trm_seq (foreign key to train_mod table) > ... and some other title, desc etc fields > > train_mod > trm_seq_no (primary key) > trm_trc_seq (foreign key to train_comp table) > .. and title etc fields > > train_comp > trc_seq_no (primary key) > .. and title etc fields [snip] >>> 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 Hi Christine, I can't see it either, and without any data to try it on I'm really only guessing.... Try removing the WHERE clause and see if you get any rows back. Also, try each join separately - just two tables at a time - and see what happens. Hope this helps.... Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
I decided to approach this a different way. Not worth the time to figure out. I get all records from all tables and put them together in the program using this. Thanks for your help. Christine At 12:20 PM 16/09/2010, you wrote: >On 16/09/2010 18:33, Christine Penner wrote: >>Training Course >>tc_seq_no (primary key) >>tc_trm_seq (foreign key to train_mod table) >>... and some other title, desc etc fields >> >>train_mod >>trm_seq_no (primary key) >>trm_trc_seq (foreign key to train_comp table) >>.. and title etc fields >> >>train_comp >>trc_seq_no (primary key) >>.. and title etc fields > >[snip] > >>>>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 > >Hi Christine, > >I can't see it either, and without any data to try it on I'm really >only guessing.... > >Try removing the WHERE clause and see if you get any rows back. > >Also, try each join separately - just two tables at a time - and see >what happens. > >Hope this helps.... > >Ray. > > >-- >Raymond O'Donnell :: Galway :: Ireland >rod@iol.ie