From: | Tom Hart <tomhart(at)coopfed(dot)org> |
---|---|
To: | PG-SQL List <pgsql-sql(at)postgresql(dot)org> |
Subject: | outer join issues |
Date: | 2008-02-06 21:36:36 |
Message-ID: | 47AA2864.3080806@coopfed.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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)
From | Date | Subject | |
---|---|---|---|
Next Message | Colin Wetherbee | 2008-02-06 22:02:22 | Re: outer join issues |
Previous Message | D'Arcy J.M. Cain | 2008-02-06 21:17:10 | Re: Double query |