Left outer join and conditions

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

SQL Server 2005 Forums

Transact-SQL (2005)

Left outer join and conditions

Author

Topic

sue1127
Starting Member

23 Posts

Posted - 2008-03-17 : 14:40:31

I am using Transact-Sql 2005, and I'm trying to do a left outer join, including only certain accounts. The account number is x-xxx-xxxx-xxxx.
I want to include only accounts where the last 4 digits are > 7149, and the first 5 digits are between 2-110 and 2-999, or are equal to 8-001.

This is my code, based on 2 temporary tables I have previously populated:

select
b.gl7accountsid,
b.accountnumber,
t.description,
t.category,
t.postdate,
t.poststatus,
t.transactiontype,
t.transamount,
coalesce(t.transamount,0) as TransactionAmount,
t.encumbrancestatus,
t.gl7fiscalperiodsid,
b.budamount,
b.gl7budgetscenariosid

from
#budgets b
left outer
join
#transactions t
on t.accountnumber=b.accountnumber
And right(t.accountnumber,4) > 7149
and (left(t.accountnumber,5) between '2-110' and '2-999' or left(t.accountnumber,5)='8-001')
order by b.accountnumber

I keep getting accounts with the last 4 digits > 7149, and also accounts whose numbers don't fall into the desired group of the first 5 digits.

I'm fairly new to SQL, and I'm thinking there must be a way to do what I want to do, but I don't know what it is.

Can anyone help?

Thanks very much ,

Sue

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-03-17 : 14:46:06

Try the following:

select
b.gl7accountsid,
b.accountnumber,
t.description,
t.category,
t.postdate,
t.poststatus,
t.transactiontype,
t.transamount,
coalesce(t.transamount,0) as TransactionAmount,
t.encumbrancestatus,
t.gl7fiscalperiodsid,
b.budamount,
b.gl7budgetscenariosid

from
#budgets b
left outer
join
#transactions t
on t.accountnumber=b.accountnumber
And right(t.accountnumber,4) > 7149
and (left(t.accountnumber, 1) = 2 and substring(t.accountnumber, 3, 3) > 109 or left(t.accountnumber,5)='8-001')
order by b.accountnumber

sue1127
Starting Member

23 Posts

Posted - 2008-03-17 : 14:57:44

I tried, but no luck. I'm still getting accounts that begin with 2-001, and have the last 4 digits < 7149, and accounts that begin with 4 or 6

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-03-17 : 15:11:32

Most likely you are looking at b.accountnumber which is going to still be retained in your dataset because you are using a left outer join. All columns from #transactions should be null for records that dont meet your criteria.

If you want them excluded from your dataset you should move "right(t.accountnumber,4) > 7149 and (left(t.accountnumber, 1) = 2 and substring(t.accountnumber, 3, 3) > 109 or left(t.accountnumber,5)='8-001')" to a where clause like so:

select
b.gl7accountsid,
b.accountnumber,
t.description,
t.category,
t.postdate,
t.poststatus,
t.transactiontype,
t.transamount,
coalesce(t.transamount,0) as TransactionAmount,
t.encumbrancestatus,
t.gl7fiscalperiodsid,
b.budamount,
b.gl7budgetscenariosid

from
#budgets b
left outer
join
#transactions t
on t.accountnumber=b.accountnumber
WHERE right(t.accountnumber,4) > 7149
and (left(t.accountnumber, 1) = 2 and substring(t.accountnumber, 3, 3) > 109 or left(t.accountnumber,5)='8-001')
order by b.accountnumber

sue1127
Starting Member

23 Posts

Posted - 2008-03-17 : 15:20:16

Yes, that worked!

Thanks so much for your help.

Sue

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-17 : 19:50:06

You now need to ask yourself whether it needs to still be an outer join.
Consider: You outer join to create a 'dummy' null filled T. you then start comparing t.accountnumber to constants. NULL will never fulfill these conditions, so no 'dummied up' rows will ever be returned.
I suggest you turn it back to an inner join to give the optimiser more options (i.e. chosing b or t to drive the query). It may be clever enough to work all this out but even so it makes sense for your own understanding and for those who come after you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources