在sql server 2008中，我有以下查询：
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
我正在尝试获取其子类别中的项目，但是如果该类别或子类别中没有项目，我仍然想返回一条记录。没有项目的子类别永远不会返回。我究竟做错了什么？
谢谢
编辑
修改后的查询带有第二个左连接和where子句，但仍不返回null。：/
编辑2
将siteid移至项目左联接。当我这样做时，我会得到比预期更多的记录。有些项目的网站ID为空，我只想在它们具有特定ID时将其包括在内。
编辑3
表结构：
Categories Table ------- CategoryID Title SubCategories Table ------- SubCategoryID CategoryID Title ItemCategories Table ------- ItemCategoryID ItemID SubCategoryID IsActive Items Table -------- ItemID Title SiteID
将join items i…更改为LEFT join items i…，您的查询应会按预期工作。
join items i
LEFT join items i
编辑 除非除非考虑到空值，否则您不能在where子句中过滤LEFT JOIN表，因为左连接允许这些列具有值，或者当没有行匹配时为空：
and i.siteid = 132 将丢弃所有i.siteid不存在的NULL的行。将其移动到ON：
and i.siteid = 132
i.siteid
left join items i on ic.itemid = i.itemid and i.siteid = 132
或使WHERE处理NULL：
WHERE ... AND (i.siteid = 132 OR i.siteid IS NULL)
编辑 基于OP的编辑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
我不确定100％的操作是什么，这将返回所有可以siteid=132在问题中给出的信息加入的信息
siteid=132
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
输出：
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)
即使没有与之匹配的内容，也会列出所有类别 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
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)
