Адресная книга: Превратите запрос «достаточно хотя бы одного списка» в «все списки должны присутствовать»
У меня есть база данных SQLite3, которая содержит, среди прочего, следующие пять таблиц: SQL-схема адресной книги
Списки можно назначать контактам и заданиям на печать. Если я хочу обработать задание на печать (например, распечатать контакты на конвертах), мне нужно получить все те контакты, которым присвоены те же списки, что и этому заданию на печать.
До сих пор я получил этот SQL-запрос, но он извлекает все контакты, которые имеют один общий список по меньшей мере, это рассматриваемое задание печати, но мне нужны назначенные списки все для соответствия:
SELECT DISTINCT `contact`.* FROM `contact` JOIN (
SELECT `contact_id` FROM `contact_list` JOIN (
SELECT `list_id` FROM `job_list` WHERE `job_id` = :id
) AS `inner` ON `inner`.`list_id` = `contact_list`.`list_id`
) AS `outer` ON `outer`.`contact_id` = `contact`.`id`
Я рассматриваю это как «по крайней мере, один список достаточно хорош», но мне нужно, чтобы «все списки также были назначены для задания».
Как выглядит такой запрос?
Редактировать:
Вот несколько запросов, которые генерируют образцы данных: Пример данных SQL
И вот ожидаемые результаты для каждого задания на печать:
job_id contact_id
1 1, 2, 3
2 5
3 4, 6
Контакт 7 никогда не должен появляться
Результат
SELECT * FROM contact WHERE id IN (
WITH flattenJob (job_id, jlist) AS (
SELECT job_id, group_concat(list_id)
FROM job_list
GROUP BY job_id
),
flattenCont (contact_id, clist) AS (
SELECT contact_id, group_concat(list_id)
FROM contact_list
GROUP BY contact_id
)
SELECT contact_id
FROM flattenJob
JOIN flattenCont ON jlist = clist
WHERE job_id = :id
)
ORDER BY name
Ответы 1
Похоже, цель состоит в том, чтобы напечатать по одному конверту для каждого контакта (сэкономить деньги, сохранить окружающую среду, ура!); по существу дедупликации списков. Идея состоит в том, чтобы превратить «составную работу» (например, работу 3) в собственную сущность. Один из способов — использовать виртуальные таблицы, например:
WITH flattenJob (job_id,jlist) as
(select job_id,group_concat(list_id)
from job_list
group by job_id
),
flattenCont (contact_id,clist)
as (select contact_id,group_concat(list_id)
from contact_list
group by contact_id
)
select job_id,contact_id,jlist,clist
from flattenJob
JOIN flattenCont on jlist = clist
Это результат выборки данных:
job_id contact_id jlist clist
---------- ---------- ---------- ----------
1 1 1 1
1 2 1 1
1 3 1 1
2 5 2 2
3 4 1,2 1,2
3 6 1,2 1,2
Это должно дать вам хорошую отправную точку для создания успешного запроса.
Спасибо за ответ. Я отредактировал свой оригинальный пост.