Почему мое левое соединение не возвращает нули?

В 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 к элементу left join. Когда я делаю это, я получаю больше записей, чем ожидалось. Некоторые элементы имеют нулевой siteid, и я хочу включать их только тогда, когда они имеют определенный идентификатор.

РЕДАКТИРОВАТЬ 3

Структура таблицы:

Categories Table 
-------
CategoryID
Title

SubCategories Table
-------
SubCategoryID
CategoryID
Title

ItemCategories Table
-------
ItemCategoryID
ItemID
SubCategoryID
IsActive

Items Table 
--------
ItemID
Title
SiteID
Источник

6 ответов

Решение

Менять join items i... чтобы LEFT join items i... и ваш запрос должен работать так, как вы ожидаете.

РЕДАКТИРОВАТЬ
Вы не можете фильтровать таблицы LEFT JOIN в предложении where, если не учитываете пустые значения, потому что левое соединение позволяет этим столбцам иметь значение или быть нулевым, если ни одна строка не соответствует:

and i.siteid = 132 выбросит любые ваши строки, которые имеют значение NULL i.siteidгде ни один не существовал. Переместите это в ON:

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)

РЕДАКТИРОВАТЬ на основе редактирования ОП 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 как указано в вопросе

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 имеет значение null или 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

РЕДАКТИРОВАТЬ:

Теперь вы выбираете только существующие идентификаторы сайта в предложении where:

i.siteid = 132

Это должно разрешить нулевые значения, попробуйте что-то вроде этого:

(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 в предложении where по существу сводит на нет выполнение левого объединения элементов.

Источник

2-я попытка, я думаю, у меня сейчас возникла ваша проблема Если я правильно понимаю, то происходит то, что в вашем случае вы получаете два типа NULL в SiteID (случай, когда вы видите результаты в 10000e, но это все еще на правильном пути).

Есть NULL, которые приходят из левого соединения, и те, которые происходят из фактического siteid таблицы Item. Вы хотите, чтобы те, которые исходили из левого соединения, но не нужны из данных.

Это очень распространенная ошибка с внешними объединениями при проверке на наличие совпавших строк.

Имейте в виду, что если вам нужны несопоставленные строки, которые всегда должны проверяться на NULL только в столбцах, которые определены как NOT NULL (первичный ключ из внешней таблицы является естественным кандидатом здесь). В противном случае вы не сможете различить строки, которые имеют значение NULL из-за LEFT-соединения, и строки, которые будут иметь значение NULL, даже если это было INNER-соединение

По крайней мере, есть два способа сделать это: а) левое соединение в подзапросе, который отфильтровывает строки с siteid, равно нулю, прежде чем левое соединение вступит в силу
б) переписать критерии (при условии, что ItemID требуется в Items), чтобы сказать

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 - нулевым).

Источник

Попробуйте изменить соединение элементов на левое соединение.

Источник
Другие вопросы по тегам