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

Try changing the join of Items to a left join as well.

Answer 3

Maybe this join also should be left join?

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

EDIT:

Now you are selecting only existing site ids in where clause:

i.siteid = 132

It should allow null values, try smth like this:

(i.siteid = 132 or i.siteid is null)

or you could move i.siteid = 132 back to join condition

Answer 4

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

Answer 5

where (ic.isactive = 1 or ic.isactive is null) and i.siteid = 132

Having the i.siteID = 132 in your where clause essentially nullifies doing a left join on items.

Answer 6

2nd attempt, I think I got your problem now. If I understand correctly then what happens is that you end up with two kind of NULLs in SiteID in your case (the case when you see 10,000e results, but that is still on the right track).

There are NULLs that come from left join and the ones that come from actual Item table siteid. You want to have the ones that come from left join, but don't want the ones from data.

This is a very common mistake with outer joins when testing for existence of matched rows.

Keep in mind that you if you want unmatched rows that should always test for NULL only on the columns which are defined as NOT NULL (primary key from the outer table is a natural candidate here). Otherwise you can not distinguish between the rows that are NULL due to LEFT join and rows which would be NULL even if it was an INNER join

At least two ways to go about this: a) left join on subquery that will filter out rows with siteid is null before the left join kicks in
b) rewrite criteria (assuming ItemID is required in Items) to say

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
where (ic.isactive = 1 or ic.isactive is null) AND (i.siteid = 132 or i.itemid is null)
order by c.title, s.title

(I am assuming that the query up to the join to items table was giving you what you expected).

If itemid is required in items then the above condition says - rows with siteid 132 or rows that really come from unmatched left join (do note that the condition is on i.itemid is null and not i.siteid is null).