NOT EXISTS function not working

[Bomb_shell]

Okay, someone straighten me out. I have two tables: [Budget] is the monthly budget for a bunch of accounts and [Account List] is (duh) the master list of accounts. I need to find the account names, [Twin Pack], that do not have any budget, ie no record exists, for a specified date, [Date]. Here's what I wrote:

SELECT [Account List].[Twin Pack]
FROM [Account List]
WHERE NOT EXISTS (SELECT Budget.[Twin Pack]
FROM Budget
WHERE (((Budget.Month)=DateValue([Date]))));

When I run it as is, it returns nothing. To troubleshoot, I put the subquery in a query by itself and got the desired results: the list of accounts that DO have budget in the specified month. Also, when I reversed the EXISTS statement by taking out the "Not", I also got the list of accounts that DO have budget in the specified month. What am I missing?

[Mycroft Holmes]

You seem to have 2 disassociated queries here, one on Account List and another on Budget with no logic to join them. [rant] Who in his right mind allows spaces in table/field names and what idiot allows reserved words to be used as field names (Date) [/rant]

SELECT [Account List].[Twin Pack]
FROM [Account List]
WHERE something NOT EXISTS (SELECT Budget.[Twin Pack]
FROM Budget
WHERE (((Budget.Month)=DateValue([Date]))));

also make sure your internal select returns the expected records (what is DateValue)

[Niladri_Biswas]

Try this declare @tblAccountList table(TwinPack int) insert into @tblAccountList select 111 union all select 222 union all select 333 union all select 444 union all select 555 union all select 666 union all select 777 union all select 888 union all select 1100 declare @tblBudget table(TwinPack int,Mnth varchar(20)) insert into @tblBudget select 111,'Jan' union all select 222,'Feb' union all select 333,'Mar' union all select 444,'Apr' union all select 555,'May' union all select 666, 'Jun' union all select 777,'Jul' union all select 888 ,'Aug' union all select 999,'Sep' union all select 1000 ,'Oct' union all select 1100,'Nov' union all select 1200 ,'Dec'

SELECT a.TwinPack
FROM @tblAccountList a
except
SELECT b.TwinPack
FROM @tblBudget b
WHERE b.Mnth = CONVERT(varchar(3),DATENAME(mm,GETDATE()))

Basically I am finding out the TwinPack from @tblAccountList where the budget is not in November. I am using Except here Note- I have done the schema by myself which I hope is as per ur requirement.Else you please correct and I will again give a shot. Hope this helps :)

Niladri Biswas

[Niladri_Biswas]

You can even look into this http://stackoverflow.com/questions/1662902/when-to-use-except-as-opposed-to-not-exists-in-transact-sql[^] :)

Niladri Biswas

[Ashfield]

Try this

SELECT [Account List].[Twin Pack]
FROM [Account List]
WHERE NOT EXISTS (SELECT 1 from Budget
WHERE Budget.[Twin Pack] = [Account List].[Twin Pack]
and Budget.Month=DateValue([Date])

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Bomb_shell]

Mycroft Holmes wrote:

Who in his right mind allows spaces in table/field names and what idiot allows reserved words to be used as field names (Date)

Yeah, I'm right up there on that soap box with ya. It's a linked table that someone else created. I could write some code to go change the field names, but this was supposed to be a quick and easy DB. I found out what I was missing. I scrapped the first approach and did a Left Join on the two tables.