pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedouter join issues
4+ messages / 3 participants
[nested] [flat]
* outer join issues
@ 2008-02-06 21:36 Tom Hart <tomhart@coopfed.org>
2008-02-06 22:02 ` Re: outer join issues Colin Wetherbee <cww@denterprises.org>
2008-02-06 22:22 ` Re: outer join issues Terry Fielder <terry@ashtonwoodshomes.com>
0 siblings, 2 replies; 4+ messages in thread
From: Tom Hart @ 2008-02-06 21:36 UTC (permalink / raw)
To: 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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: outer join issues
2008-02-06 21:36 outer join issues Tom Hart <tomhart@coopfed.org>
@ 2008-02-06 22:02 ` Colin Wetherbee <cww@denterprises.org>
2008-02-06 22:37 ` Re: outer join issues Tom Hart <tomhart@coopfed.org>
1 sibling, 1 reply; 4+ messages in thread
From: Colin Wetherbee @ 2008-02-06 22:02 UTC (permalink / raw)
To: Tom Hart <tomhart@coopfed.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: outer join issues
2008-02-06 21:36 outer join issues Tom Hart <tomhart@coopfed.org>
2008-02-06 22:02 ` Re: outer join issues Colin Wetherbee <cww@denterprises.org>
@ 2008-02-06 22:37 ` Tom Hart <tomhart@coopfed.org>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Hart @ 2008-02-06 22:37 UTC (permalink / raw)
To: Colin Wetherbee <cww@denterprises.org>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: outer join issues
2008-02-06 21:36 outer join issues Tom Hart <tomhart@coopfed.org>
@ 2008-02-06 22:22 ` Terry Fielder <terry@ashtonwoodshomes.com>
1 sibling, 0 replies; 4+ messages in thread
From: Terry Fielder @ 2008-02-06 22:22 UTC (permalink / raw)
To: Tom Hart <tomhart@coopfed.org>; +Cc: pgsql-sql
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.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-02-06 22:37 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-06 21:36 outer join issues Tom Hart <tomhart@coopfed.org>
2008-02-06 22:02 ` Colin Wetherbee <cww@denterprises.org>
2008-02-06 22:37 ` Tom Hart <tomhart@coopfed.org>
2008-02-06 22:22 ` Terry Fielder <terry@ashtonwoodshomes.com>
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