Help using an outer join when using a where clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help using an outer join when using a where clause

Author

Topic

plawrenz
Starting Member

15 Posts

Posted - 2008-03-07 : 14:42:02

I am trying to get all of the Fund_cdes to show up even if there was no transaction on the brkg fact table. The problem I coming up with is I am also retricting what I show in the brkg fact table so I am not getting all of the row from the fund table. How do I write a left join that shows all of the fund cdes

SELECT
SEP_ACCOUNT.sep_acct_cde as Account,
FUND.fund_cde as FUND,
BRKG_FACT.accum_unit_cnt as Units_Purchased,
BRKG_FACT.transaction_amt as Amount_Purchased
FROM
BRKG_FACT
SEP_ACCOUNT
FUND
where
BRKG_FACT.sep_acct_id_num = SEP_ACCOUNT.sep_acct_id_num
brkg_fact.FUND_ID_NUM = FUND_DIM.FUND_ID_NUM
brkg_fact.SEP_ACCT_ID_NUM = 5 and
brkg_fact.product_cde <> 'MM' and
brkg_fact.transaction_amt <= 0 and
brkg_fact.source_sys_id_num <> 3 and
brkg_fact.source_sys_id_num <> 5 and
BRKG_FACT.trans_process_dte >= '1/1/2008' and
BRKG_FACT.trans_process_dte <= '1/2/2008'
order by fund_cde

current output

ACCOUNT FUND UNITS_PURCHASED AMOUNT_PURCHASED
U BLCD -0.01137 -1.48000
U BOND -0.01283 -1.67000
U CGDE -0.06743 -0.95000
U EQIN -0.13277 -2.39000
U GRST -0.11799 -4.07000
U IX4S -0.53996 -12.55000
U LCCS -0.18216 -5.31000

wanted output
ACCOUNT FUND UNITS_PURCHASED AMOUNT_PURCHASED
U BLCD -0.01137 -1.48000
U BOND -0.01283 -1.67000
U BWDS NULL NULL
U CGDE -0.06743 -0.95000
U EQIN -0.13277 -2.39000
U GAFR NULL NULL
U GRST -0.11799 -4.07000
U IX4S -0.53996 -12.55000
U LIGE NULL NULL
U LCCS -0.18216 -5.31000

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-03-07 : 15:23:54

Code like a pro. Use JOINS:

SELECT	SEP_ACCOUNT.sep_acct_cde as Account,
		FUND.fund_cde as FUND,
		BRKG_FACT.accum_unit_cnt as Units_Purchased,
		BRKG_FACT.transaction_amt as Amount_Purchased
FROM	FUND
		left outer join BRKG_FACT
			on FUND_DIM.FUND_ID_NUM = brkg_fact.FUND_ID_NUM
				and brkg_fact.SEP_ACCT_ID_NUM = 5
				and brkg_fact.product_cde <> 'MM'
				and brkg_fact.transaction_amt <= 0
				and brkg_fact.source_sys_id_num <> 3
				and brkg_fact.source_sys_id_num <> 5
				and BRKG_FACT.trans_process_dte >= '1/1/2008'
				and BRKG_FACT.trans_process_dte <= '1/2/2008'
		left outer join SEP_ACCOUNT on BRKG_FACT.sep_acct_id_num = SEP_ACCOUNT.sep_acct_id_num
order by fund_cde

e4 d5 xd5 Nf6

plawrenz
Starting Member

15 Posts

Posted - 2008-03-07 : 15:47:16

Thank you, that worked I just wasn't putting it in the correct order.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources