outer join issues
От | Tom Hart |
---|---|
Тема | outer join issues |
Дата | |
Msg-id | 47AA2864.3080806@coopfed.org обсуждение исходный текст |
Ответы |
Re: outer join issues
Re: outer join issues |
Список | pgsql-sql |
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)
В списке pgsql-sql по дате отправления: