Thread: outer join issues

outer join issues

From
Tom Hart
Date:
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)



Re: outer join issues

From
Colin Wetherbee
Date:
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


Re: outer join issues

From
Terry Fielder
Date:

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.
>


Re: outer join issues

From
Tom Hart
Date:
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)