From: Terry Fielder <terry@ashtonwoodshomes.com>
To: Tom Hart <tomhart@coopfed.org>
Cc: PG-SQL List <pgsql-sql@postgresql.org>
Subject: Re: outer join issues
Date: Wed, 06 Feb 2008 17:22:49 -0500
Message-ID: <47AA3339.3010307@ashtonwoodshomes.com> (raw)
In-Reply-To: <47AA2864.3080806@coopfed.org>
References: <47AA2864.3080806@coopfed.org>
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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: terry@ashtonwoodshomes.com, tomhart@coopfed.org
Subject: Re: outer join issues
In-Reply-To: <47AA3339.3010307@ashtonwoodshomes.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox