Обсуждение: outer join issues
Let me preface this by saying hello SQL list, and I'm an idiot. My SQL knowledge is advanced to the point of being able to use a WHERE clause basically, so I appreciate your business. Now on to my issue I have 3 tables I'm trying to use in this query: loan, share and draft (for those of you not familiar with credit unions, share and draft are savings and checking accounts). What I'm trying to do is get a list of all loans that were charged off (ln_chgoff_dt > 0), and any share and draft accounts that have the same account number. My query looks something like this SELECT ln_acct_num, ln_num, ln_chrgoff_dt, ln_chrgoff_amt, sh_balance, sh_stat_cd, df_balance, df_stat_cd FROM loan LEFT OUTER JOIN share ON loan.ln_acct_num = share.sh_acct_num LEFT OUTER JOIN draft ON loan.ln_acct_num = draft.df_acct_num WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 AND share.dataset = 0 AND draft.dataset = 0 ; Now the query SELECT * FROM loan WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 returns 139 rows. Shouldn't the first query return at least that many? My understanding is that a LEFT OUTER JOIN will not drop any records that are only found in the first table, regardless of whether they match records on the second or third table. I end up with 14 results with the first query. I know I'm doing something wrong, but I'm not sure what. Anybody have a helpful kick in the right direction for me? Thanks in advance. -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax)
Tom Hart wrote: > Let me preface this by saying hello SQL list, and I'm an idiot. My SQL > knowledge is advanced to the point of being able to use a WHERE clause > basically, so I appreciate your business. Now on to my issue > > I have 3 tables I'm trying to use in this query: loan, share and draft > (for those of you not familiar with credit unions, share and draft are > savings and checking accounts). What I'm trying to do is get a list of > all loans that were charged off (ln_chgoff_dt > 0), and any share and > draft accounts that have the same account number. My query looks > something like this > > SELECT ln_acct_num, ln_num, ln_chrgoff_dt, ln_chrgoff_amt, sh_balance, > sh_stat_cd, df_balance, df_stat_cd > FROM loan > LEFT OUTER JOIN share ON loan.ln_acct_num = share.sh_acct_num > LEFT OUTER JOIN draft ON loan.ln_acct_num = draft.df_acct_num > WHERE > ln_chrgoff_dt > 0 > AND loan.dataset = 0 > AND share.dataset = 0 > AND draft.dataset = 0 > ; > > Now the query > SELECT * FROM loan WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 > returns 139 rows. Shouldn't the first query return at least that many? > My understanding is that a LEFT OUTER JOIN will not drop any records > that are only found in the first table, regardless of whether they match > records on the second or third table. I end up with 14 results with the > first query. I know I'm doing something wrong, but I'm not sure what. > Anybody have a helpful kick in the right direction for me? My "I looked at this for 20 seconds" guess is that the following clauses are messing you up. > AND share.dataset = 0> AND draft.dataset = 0 The LEFT OUTER JOIN isn't helping you if you're still comparing values in the JOINed tables in the WHERE clause. Colin
Tom Hart wrote: > Let me preface this by saying hello SQL list, and I'm an idiot. My SQL > knowledge is advanced to the point of being able to use a WHERE clause > basically, so I appreciate your business. Now on to my issue > > I have 3 tables I'm trying to use in this query: loan, share and draft > (for those of you not familiar with credit unions, share and draft are > savings and checking accounts). What I'm trying to do is get a list of > all loans that were charged off (ln_chgoff_dt > 0), and any share and > draft accounts that have the same account number. My query looks > something like this > > SELECT ln_acct_num, ln_num, ln_chrgoff_dt, ln_chrgoff_amt, sh_balance, > sh_stat_cd, df_balance, df_stat_cd > FROM loan > LEFT OUTER JOIN share ON loan.ln_acct_num = share.sh_acct_num > LEFT OUTER JOIN draft ON loan.ln_acct_num = draft.df_acct_num > WHERE > ln_chrgoff_dt > 0 > AND loan.dataset = 0 > AND share.dataset = 0 > AND draft.dataset = 0 > ; try AND (share.dateset = 0 OR share.dataset IS NULL) AND (draft.dataset = 0 OR draft.dataset IS NULL) because when the left join is utilized, the dateset field will be a null, which is not =0 and hence would fail the AND clause in your version Terry > > Now the query > SELECT * FROM loan WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 > returns 139 rows. Shouldn't the first query return at least that many? > My understanding is that a LEFT OUTER JOIN will not drop any records > that are only found in the first table, regardless of whether they > match records on the second or third table. I end up with 14 results > with the first query. I know I'm doing something wrong, but I'm not > sure what. Anybody have a helpful kick in the right direction for me? > > Thanks in advance. >
Colin Wetherbee wrote: > Tom Hart wrote: >> Let me preface this by saying hello SQL list, and I'm an idiot. My >> SQL knowledge is advanced to the point of being able to use a WHERE >> clause basically, so I appreciate your business. Now on to my issue >> >> I have 3 tables I'm trying to use in this query: loan, share and >> draft (for those of you not familiar with credit unions, share and >> draft are savings and checking accounts). What I'm trying to do is >> get a list of all loans that were charged off (ln_chgoff_dt > 0), and >> any share and draft accounts that have the same account number. My >> query looks something like this >> >> SELECT ln_acct_num, ln_num, ln_chrgoff_dt, ln_chrgoff_amt, >> sh_balance, sh_stat_cd, df_balance, df_stat_cd >> FROM loan >> LEFT OUTER JOIN share ON loan.ln_acct_num = share.sh_acct_num >> LEFT OUTER JOIN draft ON loan.ln_acct_num = draft.df_acct_num >> WHERE >> ln_chrgoff_dt > 0 >> AND loan.dataset = 0 >> AND share.dataset = 0 >> AND draft.dataset = 0 >> ; >> >> Now the query >> SELECT * FROM loan WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 >> returns 139 rows. Shouldn't the first query return at least that >> many? My understanding is that a LEFT OUTER JOIN will not drop any >> records that are only found in the first table, regardless of whether >> they match records on the second or third table. I end up with 14 >> results with the first query. I know I'm doing something wrong, but >> I'm not sure what. Anybody have a helpful kick in the right direction >> for me? > > My "I looked at this for 20 seconds" guess is that the following > clauses are messing you up. > > > AND share.dataset = 0 > > AND draft.dataset = 0 > > The LEFT OUTER JOIN isn't helping you if you're still comparing values > in the JOINed tables in the WHERE clause. > > Colin Thank you for the responses everybody. I actually had somebody in the irc channel help me figure this one out. For the record here's my complete query (there's more additions, like a couple CASE's to translate numbers to strings) SELECT ln_acct_num, ln_num, ln_chrgoff_dt, ln_chrgoff_amt, sh_balance, CASE WHEN sh_stat_cd = 0 THEN 'Open' ELSE'Closed' END as sh_stat_cd, COALESCE(df_balance::text, 'No Draft'), CASE WHEN df_stat_cd = 0 THEN 'Open' ELSE 'Closed'END as df_stat_cd FROM loan LEFT OUTER JOIN (select * from share where dataset = 0) as share ON loan.ln_acct_num = share.sh_acct_num LEFT OUTER JOIN (select * from draft where dataset = 0) as draft ON loan.ln_acct_num = draft.df_acct_num WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 AND sh_balance IS NOT NULL ; -- Tom Hart IT Specialist Cooperative Federal 723 Westcott St. Syracuse, NY 13210 (315) 471-1116 ext. 202 (315) 476-0567 (fax)