sql – 为什么我的左连接没有返回空值？

在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

解决方法

编辑
除非考虑空值,否则不能在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)

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!