在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子句修改了查询,但它仍然没有返回空值. ：/
编辑2
将siteid移动到项目左连接.当我这样做时,我获得了比预期更多的记录.有些项目具有空的siteid,我只想在具有特定ID时包含它们.
编辑3
表结构：
Categories Table ------- CategoryID Title SubCategories Table ------- SubCategoryID CategoryID Title ItemCategories Table ------- ItemCategoryID ItemID SubCategoryID IsActive Items Table -------- ItemID Title SiteID
解决方法
更改联接项目i …到LEFT加入项目… …您的查询应该按预期工作.
编辑
除非考虑空值,否则不能在where子句中过滤LEFT JOIN表,因为左连接允许这些列具有值,或者在没有行匹配时为空：
并且i.siteid = 132将丢弃任何具有NULL i.siteid的行,其中不存在任何行.将其移至ON：
在ic.itemid = i.itemid和i.siteid = 132上左加入项目i
或使WHERE句柄为NULL：
WHERE … AND(i.siteid = 132或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,'SubCat AAA B') INSERT @SubCategories VALUES (3,'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,'Y') INSERT @ItemCategories VALUES (2,'Y') INSERT @ItemCategories VALUES (3,3,'Y') INSERT @ItemCategories VALUES (4,4,'Y') INSERT @ItemCategories VALUES (5,7,'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％确定OP之后是什么,这将返回当问题中给出的siteid = 132时可以加入的所有信息
SELECT
c.title as categorytitle,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)
这将列出所有类别,即使与siteid = 132不匹配
;WITH AllItems AS
(
SELECT
s.CategoryID,ItemCategoryID,i.ItemID,c.title AS categorytitle,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)
