Блог

У меня есть база данных SQLite3, которая содержит, среди прочего, эти пять таблиц: Адресная книга SQL Schema

Списки могут быть назначены контактам и заданиям на печать. Если я хочу обработать задание на печать (например, печать контактов на конвертах) Мне нужно получить все те контакты, которым были назначены те же списки, что и для этого задания на печать.

Пока я получил этот 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
  

Похоже, цель состоит в том, чтобы распечатать один конверт на контакт (сэкономить деньги, сохранить окружающую среду, Ура!); По сути, дедуплицировать списки. Идея состояла бы в том, чтобы превратить «составное задание» (например, задание 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
  

Это должно дать вам хорошую отправную точку для создания успешного запроса.