Message-ID: <47AA2E6E.7020402@denterprises.org>
Date: Wed, 06 Feb 2008 17:02:22 -0500
From: Colin Wetherbee <cww@denterprises.org>
User-Agent: Thunderbird 2.0.0.9 (Macintosh/20071031)
MIME-Version: 1.0
To: Tom Hart <tomhart@coopfed.org>
CC: PG-SQL List <pgsql-sql@postgresql.org>
Subject: Re: outer join issues
References: <47AA2864.3080806@coopfed.org>
In-Reply-To: <47AA2864.3080806@coopfed.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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