Having Clause Issue

[nerdalert1]

Hello all. I am trying to recreate an access query in SQL. The query below where you see the HAVING clause is causing less records to come out then without it, however there is no Null values in that table "Bud_State Allocation" that is referenced in the Having clause but its still returning less records. Now in SQL the same field is an Integer value. Any ideas?

SELECT Total_2004_Budget_Allocated_A.[Business Unit], Total_2004_Budget_Allocated_A.Account, Total_2004_Budget_Allocated_A.[Account Title], Total_2004_Budget_Allocated_A.DeptID, Total_2004_Budget_Allocated_A.ChargeToDeptID, Total_2004_Budget_Allocated_A.[Activity Code], SumTotal_2004_Budget_Allocated_A.[Original Activity Code], "Direct" AS Method
FROM Total_2004_Budget_Allocated_A LEFT JOIN [Bud_State Allocation] ON Total_2004_Budget_Allocated_A.ChargeToDeptID = [Bud_State Allocation].DeptID
GROUP BY Total_2004_Budget_Allocated_A.[Business Unit], Total_2004_Budget_Allocated_A.Account, Total_2004_Budget_Allocated_A.[Account Title], Total_2004_Budget_Allocated_A.DeptID, Total_2004_Budget_Allocated_A.ChargeToDeptID, Total_2004_Budget_Allocated_A.[Activity Code], Total_2004_Budget_Allocated_A.[Original Activity Code], "Direct", [Bud_State Allocation].DeptID
HAVING ((([Bud_State Allocation].DeptID) Is Null));

 

[SQLSister]

Might help to see sample data, results you are getting and results you want.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[SonOfEmidec1100]

Could it be because you are doing a LEFT JOIN there are rows in Total_2004_Budget_Allocated_A that have no joins to the Bud_State Allocation table then the DeptID would be NULL.

Add the DeptID field to the select clause and remove the having clause to check.