Why is my left join not returning nulls?

Question

In sql server 2008, I have the following query:

select           c.title as categorytitle,     s.title as subcategorytitle,     i.title as itemtitle from categories c join subcategories s on c.categoryid = s.categoryid left join itemcategories ic on s.subcategoryid = ic.subcategoryid  left join items i on ic.itemid = i.itemid and i.siteid = 132 where (ic.isactive = 1 or ic.isactive is null) order by c.title, s.title 

I am trying to get items in their subcategories, but I still want to return a record if there are no items in the category or subcategory. Subcategories that have no items are never returned. What am I doing wrong?

Thank you

EDIT

Modified query with a second left join and where clause, but it's still not returning nulls. :/

EDIT 2

Moved siteid to item left join. When I do this I get way more records than expected. Some items have a null siteid and I only want to included them when they have a specific id.

EDIT 3

Table structure:

Categories Table  ------- CategoryID Title  SubCategories Table ------- SubCategoryID CategoryID Title  ItemCategories Table ------- ItemCategoryID ItemID SubCategoryID IsActive  Items Table  -------- ItemID Title SiteID 

Answer 1

change join items i... to LEFT join items i... and your query should work as you expect.

EDIT
You can not filter LEFT JOIN tables in the where clause unless you account for nulls, because the left join allows those columns to have a value or be null when no rows matches:

and i.siteid = 132 will throw away any of your rows that have a NULL i.siteid, where none existed. Move this to the ON:

left join items i on ic.itemid = i.itemid and i.siteid = 132

or make the WHERE handle NULLs:

WHERE ... AND (i.siteid = 132 OR i.siteid IS NULL)

EDIT based on OP's edit 3

SET NOCOUNT ON DECLARE @Categories table (CategoryID int,Title varchar(30)) INSERT @Categories VALUES (1,'Cat AAA') INSERT @Categories VALUES (2,'Cat BBB') INSERT @Categories VALUES (3,'Cat CCC')  DECLARE @SubCategories table (SubCategoryID int,CategoryID int,Title varchar(30)) INSERT @SubCategories VALUES (1,1,'SubCat AAA A') INSERT @SubCategories VALUES (2,1,'SubCat AAA B') INSERT @SubCategories VALUES (3,1,'SubCat AAA C') INSERT @SubCategories VALUES (4,2,'SubCat BBB A')  DECLARE @ItemCategories table (ItemCategoryID int, ItemID int, SubCategoryID int, IsActive char(1)) INSERT @ItemCategories VALUES (1,1,2,'Y') INSERT @ItemCategories VALUES (2,2,2,'Y') INSERT @ItemCategories VALUES (3,3,2,'Y') INSERT @ItemCategories VALUES (4,4,2,'Y') INSERT @ItemCategories VALUES (5,7,2,'Y')  DECLARE @Items table (ItemID int, Title varchar(30), SiteID int) INSERT @Items VALUES (1,'Item A',111) INSERT @Items VALUES (2,'Item B',111) INSERT @Items VALUES (3,'Item C',132) INSERT @Items VALUES (4,'Item D',111) INSERT @Items VALUES (5,'Item E',111) INSERT @Items VALUES (6,'Item F',132) INSERT @Items VALUES (7,'Item G',132) SET NOCOUNT OFF 

I'm not 100% sure what the OP is after, this will return all info that can be joined when the siteid=132 as given in the question

SELECT     c.title as categorytitle         ,s.title as subcategorytitle         ,i.title as itemtitle         --,i.itemID, ic.SubCategoryID, s.CategoryID     FROM @Items                          i         LEFT OUTER JOIN @ItemCategories ic ON i.ItemID=ic.ItemID         LEFT OUTER JOIN @SubCategories   s ON ic.SubCategoryID=s.SubCategoryID         LEFT OUTER JOIN @Categories      c ON s.CategoryID=c.CategoryID     WHERE i.siteid = 132 

OUTPUT:

categorytitle                  subcategorytitle               itemtitle ------------------------------ ------------------------------ ------------------------------ Cat AAA                        SubCat AAA B                   Item C NULL                           NULL                           Item F Cat AAA                        SubCat AAA B                   Item G  (3 row(s) affected) 

This will list all categories, even if there is no match to the siteid=132

;WITH AllItems AS ( SELECT     s.CategoryID, ic.SubCategoryID, ItemCategoryID, i.ItemID         ,c.title AS categorytitle, s.title as subcategorytitle, i.title as itemtitle     FROM @Items                          i         LEFT OUTER JOIN @ItemCategories ic ON i.ItemID=ic.ItemID         LEFT OUTER JOIN @SubCategories   s ON ic.SubCategoryID=s.SubCategoryID         LEFT OUTER JOIN @Categories      c ON s.CategoryID=c.CategoryID     WHERE i.siteid = 132 ) SELECT     categorytitle, subcategorytitle,itemtitle     FROM AllItems UNION SELECT     c.Title, s.Title, null     FROM @Categories                     c         LEFT OUTER JOIN @SubCategories   s ON c.CategoryID=s.CategoryID         LEFT OUTER JOIN @ItemCategories ic ON s.SubCategoryID=ic.SubCategoryID         LEFT OUTER JOIN AllItems         i ON c.CategoryID=i.CategoryID AND  s.SubCategoryID=i.SubCategoryID     WHERE i.ItemID IS NULL ORDER BY categorytitle,subcategorytitle 

OUTPUT:

categorytitle                  subcategorytitle               itemtitle ------------------------------ ------------------------------ ------------------------------ NULL                           NULL                           Item F Cat AAA                        SubCat AAA A                   NULL Cat AAA                        SubCat AAA B                   Item C Cat AAA                        SubCat AAA B                   Item G Cat AAA                        SubCat AAA C                   NULL Cat BBB                        SubCat BBB A                   NULL Cat CCC                        NULL                           NULL  (7 row(s) affected) 

Answer 2

Your "WHERE" criteria on i.siteid means that there has to be an "items" row in the output. you need to write (i.siteid is null or i.siteid = 132) or put the "i.siteid = 132" into the "ON" clause- something that will work for the itemcategories join too:

select           c.title as categorytitle,     s.title as subcategorytitle,     i.title as itemtitle from categories c join subcategories s on c.categoryid = s.categoryid left join itemcategories ic on s.subcategoryid = ic.subcategoryid and ic.isactive = 1 left join items i on ic.itemid = i.itemid and i.siteid = 132 order by c.title, s.title