То, почему мое левое соединение, не возвращаясь, аннулирует?
В SQL-сервере 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
Я пытаюсь получить объекты в их подкатегориях, но я все еще хочу возвратить запись, при отсутствии объектов в категории или подкатегории. Подкатегории, которые не имеют никаких объектов, никогда не возвращаются. Что я делаю неправильно?
Спасибо
Править
Измененный запрос со вторым левым соединением и куда пункт, но это все еще не возвращается, аннулирует.:/
РЕДАКТИРОВАНИЕ 2
Перемещенный siteid к объекту оставил соединение. Когда я делаю это, я получаю путь больше записей, чем ожидалось. Некоторые объекты имеют пустой указатель 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... и ваш запрос должен работать так, как вы ожидаете.
EDIT
Вы не можете фильтровать таблицы LEFT JOIN в предложении where, если вы не учитываете нули, потому что левое соединение позволяет этим столбцам иметь значение или быть нулевыми, когда ни один ряд не совпадает:
и i.siteid = 132 отбросит все ваши строки, имеющие NULL i.siteid, где их не было. Переместите это в ON:
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)
EDIT на основе редактирования ОП 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
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, 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
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)
Ваш критерий «WHERE» на i.siteid означает, что в выходных данных должна быть строка «items». вам нужно написать (i.siteid имеет значение null или i.siteid = 132) или поместить «i.siteid = 132» в предложение «ON» - что-то, что будет работать и для присоединения категорий товаров:
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
where (ic.isactive = 1 or ic.isactive is null) and i.siteid = 132
Наличие i.siteID = 132 в предложении where по существу сводит на нет выполнение левого соединения для элементов.
Возможно, это соединение также должно быть левым соединением?
join items i on ic.itemid = i.itemid and i.siteid = 132
EDIT:
Теперь вы выбираете только существующие идентификаторы сайтов в условии where:
i.siteid = 132
Оно должно допускать нулевые значения, попробуйте что-то вроде этого:
(i.siteid = 132 or i.siteid is null)
или вы можете переместить i.siteid = 132 обратно в условие соединения
Попробуйте также изменить соединение Items на левое соединение.
2-я попытка, я думаю, теперь я понял вашу проблему. Если я правильно понимаю, то в вашем случае вы получаете два вида NULL в SiteID (случай, когда вы видите результаты 10 000e, но это все еще на правильном пути).
Существуют NULL, которые происходят из левого соединения, и те, которые происходят из фактического siteid таблицы Item. Вы хотите, чтобы те, которые пришли из левого соединения, но не хотели, чтобы те, которые были получены из данных.
Это очень частая ошибка с внешними соединениями при проверке существования совпадающих строк.
Имейте в виду, что если вам нужны несовпадающие строки, которые всегда должны проверять на NULL только в столбцах, которые определены как NOT NULL (первичный ключ из внешней таблицы здесь является естественным кандидатом). В противном случае вы не сможете отличить строки, которые имеют NULL из-за соединения LEFT, и строки, которые будут NULL, даже если это было INNER соединение
По крайней мере, два способа сделать это:
a) левое соединение на подзапрос, который будет отфильтровывать строки с siteid, имеет значение null до того, как левое соединение сработает в
б) критерии перезаписи (предполагая, что 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 имеет значение null, а не i.siteid равно null).