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)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
