Выберите объект, который имеет несколько отношений
Примеры таблиц:
объект
атрибут
связь
Теперь я хочу сгенерировать SQL, который возвращает только объекты, имеющие хотя бы заданный набор атрибутов. Я попробовал JOIN, но он либо ничего не возвращает, либо слишком много.
Моя попытка получить объект с атрибутами «attr0» и «attr1» (которые в данном случае должны соответствовать только obj0):
SELECT o.name
FROM link l
INNER JOIN object o ON o.id = l.o_id
INNER JOIN attr a ON a.id = l.a_id
WHERE a.description = 'attr0'
AND a.description = 'attr1'
Это ничего не возвращает, потому что после JOIN это уникальные строки. Если использовать OR вместо AND, возвращается как obj0, так и obj1. Результатом, который я хочу видеть, будет только obj0, потому что он единственный имеет attr0 и attr1.
Пример (желаемого) результата: |o.id|o.name| |:--:|:----:| | 0| объект0|
Есть ли способ сделать это внутри SQL вместо того, чтобы потом фильтровать код? Я чувствую, что делаю это через SQL, и, таким образом, в базе данных мой код будет намного быстрее.
Кстати, "attr1" будет обозначать столбец таблицы с именем attr1. Если вы хотите найти строку, используйте одинарные кавычки: WHERE a.description = 'attr1' ...
Вы хотите сгенерировать SQL, который возвращает только obj1. Это может быть простой SQL-запрос типа «Выберите имя ИЗ объекта WHERE id = 0». Пожалуйста, опишите более конкретно ваш вариант использования и отношения между вашими таблицами.
Ой, извините за цитирование. Я печатал в уме. Мое намерение было: «Найти объект, который имеет хотя бы все заданные атрибуты».
Минимальный воспроизводимый пример также должен указывать ожидаемый результат.
«Заданные атрибуты» относятся ко всем атрибутам, указанным в запросе? (Вот как я интерпретировал это в своем ответе.) Или это относится ко всем атрибутам в таблице атрибутов? (Вот как доктор интерпретировал это в своем ответе.)
@ThorstenKettner: «Заданные атрибуты» в моем примере относятся к attr1 и attr2.
что делает мой ответ недействительным.
Почему вы отметили свой запрос postgresql и sqlite? Вам по какой-то причине нужен запрос, работающий в обеих СУБД? Это кажется немного странным.
Ответы 4
Простой подход — ограничить выбор o.id = 0
SELECT
o.name AS object_name,
a.description AS attribute_description
FROM
object o
JOIN
link l ON o.id = l.o_id
JOIN
attr a ON l.a_id = a.id
WHERE
o.id = 0;
Это дало бы мне только объект 0, верно. Но я хочу фильтровать атрибуты.
Ваш запрос не будет работать из-за «И». Не существует регистра, удовлетворяющего обоим условиям. Попробуйте заменить его на «ИЛИ».
SELECT o.name
FROM link l
INNER JOIN object o ON o.id = l.o_id
INNER JOIN attr a ON a.id = l.a_id
WHERE a.description = 'attr1'
OR a.description = 'attr2'
Или лучше, вы можете использовать IN
SELECT o.name
FROM link l
INNER JOIN object o ON o.id = l.o_id
INNER JOIN attr a ON a.id = l.a_id
WHERE a.description IN ('attr1','attr2');
Затем вы можете отфильтровать нужный объект:
SELECT o.name
FROM link l
INNER JOIN object o ON o.id = l.o_id
INNER JOIN attr a ON a.id = l.a_id
WHERE a.description IN ('attr1','attr2') AND o_id=0;
Вся суть запроса в том, чтобы найти объекты, имеющие хотя бы заданный набор атрибутов. Это означает, что я не могу указать объект заранее. Мой пример – найти все объекты, у которых есть хотя бы «attr1» и «attr2».
Думаю, вам нужно что-то вроде этого — простая агрегация, сгруппированная по объектам и отфильтрованная с помощью предложения Have:
Select o.id, o.name
From object o
Inner join link l ON( l.o_id = o.id )
Group By o.id, o.name
Having Count(Distinct l.a_id) = (Select Count(id) From attr)
См. скрипку здесь.
Это будет соответствовать каждому объекту с определенным количеством атрибутов, независимо от того, какие атрибуты они имеют.
@Werdck: Нет. Это соответствует объектам, имеющим все доступные атрибуты. Вот как д.р. понял ваш комментарий к запросу «Найдите объект, который имеет хотя бы все заданные атрибуты».
Найдите объект, который имеет хотя бы все заданные атрибуты
Ваш пример запутан, так как вы ищете «attr1» и «attr2» и хотите вернуть obj1, который имеет только «attr1».
Однако вы хотите выбрать объект, поэтому выберите его из таблицы объектов. Вы хотите ограничить результат, поэтому используйте предложениеwhere. Нет необходимости присоединяться. Используйте EXISTS или IN, чтобы проверить, существуют ли для объекта нужные атрибуты:
select *
from object
where id in (select o_id from link where a_id = (select id from attr where description = 'attr1'))
and id in (select o_id from link where a_id = (select id from attr where description = 'attr2'));
То же самое с EXISTS:
select *
from object o
where exists (select null from link l where l.o_id = o.id and l.a_id = (select id from attr where description = 'attr1'))
and exists (select null from link l where l.o_id = o.id and l.a_id = (select id from attr where description = 'attr2'));
Я добавил недостающие скобки.
Я опечатался/неправильно перевел исходные имена столбцов, после исправления этой ошибки с моей стороны все заработало. Спасибо!
Я не понимаю правило, которое вы хотите применить. Объект 0 имеет два желаемых атрибута и еще один. Объект 1 имеет только один из двух атрибутов и не имеет других. Вы хотите показать объект 1. Почему?