title: Why is my left join not returning nulls?
body: <p>In sql server 2008, I have the following query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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?</p>
  
  <p>Thank you</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Modified query with a second left join and where clause, but it's still not returning nulls. :/</p>
  
  <p><strong>EDIT 2</strong></p>
  
  <p>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.</p>
  
  <p><strong>EDIT 3</strong></p>
  
  <p>Table structure:</p>
  
  <pre><code>Categories Table 
  -------
  CategoryID
  Title
  
  SubCategories Table
  -------
  SubCategoryID
  CategoryID
  Title
  
  ItemCategories Table
  -------
  ItemCategoryID
  ItemID
  SubCategoryID
  IsActive
  
  Items Table 
  --------
  ItemID
  Title
  SiteID
  </code></pre>