returns unmatched rows problem

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

returns unmatched rows problem

Author

Topic

MGA
Starting Member

28 Posts

Posted - 2011-04-03 : 18:34:51

i have three tables
Category_Types
(
Cat_Type_Id int primary key,
Cat_Type varchar(50) not null
)
Categories(
Category_Id int primary key,
Cat_Type_Id int foreign key references Category_Types(Cat_Type_Id),
Category_Name varchar(20)
)
Expenses (
Spend_Id int primary key ,
Category_Id int foreign key references Categories(Category_Id) ,
Spends money not null,
Spend_Date date not null
)
i used the following query to return the total expenses for each category types, so if no expenses from a spesific category of a category type it will be 0 in the result using isnull and left join and it works fine:
select SUM(isnull(Expenses.Spends,0)) Spends,Category_Types.Cat_Type
from Category_Types left join Categories on Category_Types.Cat_Type_Id = Categories.Cat_Type_Id
left join Expenses on Expenses.Category_Id = Categories.Category_Id
Group by Category_Types.Cat_Type

the problem is when i added the where cluase it only get the matched data from the expenses table (if a category types has no expenses for any category if it won't appear in the results ) as follows:

select SUM(isnull(Expenses.Spends,0)) Spends,Category_Types.Cat_Type
from Category_Types left join Categories on Category_Types.Cat_Type_Id = Categories.Cat_Type_Id
left join Expenses on Expenses.Category_Id = Categories.Category_Id
where Expenses.Spend_Date between '2011-03-01' and '2011-03-31'
Group by Category_Types.Cat_Type

why doesn't it works with where cluase i mean using the left join without the where cluase it returns all matching category_types even if there are no matches in the expenses tables?

so how to modify the query to return all the expenses from the category_types table, even if there are no expenses for these category_types in the expenses table.
i mean it will be 0 instead of null

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-04-03 : 19:04:46

Move the criteria to the JOIN clause instead of the WHERE clause.

SELECT ...
FROM Category_Types ct
LEFT JOIN Categories c ON c.Cat_Type_Id = ct.Cat_Type_Id
LEFT JOIN Expenses e ON e.CategoryId = c.Category_Id AND e.Spend_Date BETWEEN '20110301' AND '20110331'
GROUP BY ct.Cat_Type

The reason it does not work within the WHERE clause is because of the NULL values returned from the outer join. When you do not have a row in Expenses, the outer join returns a NULL Spend_Date and that row will be filtered out because a NULL is not equal to anything.

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources