เหตุใดการเข้าร่วมด้านซ้ายของฉันจึงไม่ส่งคืนค่าว่าง

เปลี่ยน join items i... เป็น LEFT join items i... และข้อความค้นหาของคุณควรทำงานได้ตามที่คุณคาดหวัง

แก้ไข
คุณไม่สามารถกรองตาราง LEFT JOIN ในส่วนคำสั่ง Where เว้นแต่คุณจะพิจารณาค่าว่าง เนื่องจากการรวมด้านซ้ายทำให้คอลัมน์เหล่านั้นมีค่าหรือเป็นค่าว่างเมื่อไม่มีแถวที่ตรงกัน:

and i.siteid = 132 จะทิ้งแถวใดๆ ของคุณที่มีค่า NULL i.siteid โดยไม่มีอยู่ ย้ายสิ่งนี้ไปที่เปิด:

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

หรือทำให้ WHERE จัดการ NULLs:

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% ว่า OP คืออะไร สิ่งนี้จะส่งคืนข้อมูลทั้งหมดที่สามารถเข้าร่วมได้เมื่อ 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)

เกณฑ์ "WHERE" ของคุณใน i.siteid หมายความว่าจะต้องมีแถว "items" ในเอาต์พุต คุณต้องเขียน (i.siteid เป็นโมฆะหรือ i.siteid = 132) หรือใส่ "i.siteid = 132" ในส่วนคำสั่ง "ON" - สิ่งที่จะใช้ได้กับ itemcategories เข้าร่วมด้วย:

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

บางทีการรวมนี้ควรเป็นการเข้าร่วมด้านซ้ายด้วย

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

แก้ไข:

ตอนนี้คุณกำลังเลือกเฉพาะรหัสไซต์ที่มีอยู่ในส่วนคำสั่งที่:

i.siteid = 132

ควรอนุญาตให้มีค่า Null ลองทำดังนี้:

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

หรือคุณสามารถย้าย i.siteid = 132 กลับไปที่เงื่อนไขการเข้าร่วม

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

การมี i.siteID = 132 ในส่วนคำสั่งของคุณทำให้การรวมด้านซ้ายกับรายการเป็นโมฆะ

ความพยายามครั้งที่ 2 ฉันคิดว่าฉันพบปัญหาของคุณแล้ว ถ้าฉันเข้าใจถูกต้อง สิ่งที่เกิดขึ้นก็คือคุณจะพบกับ NULL สองประเภทใน SiteID ในกรณีของคุณ (กรณีที่คุณเห็นผลลัพธ์ 10,000e แต่นั่นยังอยู่ในแนวทางที่ถูกต้อง)

มี NULL ที่มาจากการรวมด้านซ้ายและรายการที่มาจาก siteid ตารางรายการจริง คุณต้องการให้สิ่งที่มาจากการเข้าร่วมด้านซ้าย แต่ไม่ต้องการสิ่งที่มาจากข้อมูล

นี่เป็นข้อผิดพลาดทั่วไปมากกับการรวมภายนอกเมื่อทดสอบการมีอยู่ของแถวที่ตรงกัน

โปรดทราบว่าหากคุณต้องการแถวที่ไม่ตรงกันซึ่งควรทดสอบค่า NULL เสมอในคอลัมน์ที่กำหนดเป็น NOT NULL (คีย์หลักจากตารางด้านนอกคือตัวเลือกโดยธรรมชาติที่นี่) มิฉะนั้นคุณจะไม่สามารถแยกความแตกต่างระหว่างแถวที่เป็น NULL เนื่องจากการเข้าร่วม LEFT และแถวซึ่งจะเป็น NULL แม้ว่าจะเป็นการรวม INNER

อย่างน้อยสองวิธีในการดำเนินการนี้: ก) การรวมที่เหลือในแบบสอบถามย่อยที่จะกรองแถวที่มี siteid เป็นโมฆะก่อนที่การรวมด้านซ้ายจะเริ่มใน
b) เขียนเกณฑ์ใหม่ (สมมติว่าจำเป็นต้องมี ItemID ในรายการ) เพื่อพูด

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

(ฉันสมมติว่าแบบสอบถามจนถึงตารางเข้าร่วมรายการจะให้สิ่งที่คุณคาดหวัง)

หากจำเป็นต้องมี itemid ในรายการ เงื่อนไขข้างต้นจะระบุว่า - แถวที่มี siteid 132 หรือแถวที่มาจากการรวมด้านซ้ายที่ไม่ตรงกัน (โปรดทราบว่าเงื่อนไขบน i.itemid เป็นโมฆะ ไม่ใช่ i.siteid เป็นโมฆะ)

ลองเปลี่ยนการรวมของรายการเป็นการเข้าร่วมด้านซ้ายด้วย