Как выбрать различия, используя три таблицы?

#mysql #join #pivot-table

Вопрос:

Мне нужно запустить скрипт, чтобы исправить некоторые строки из моей таблицы company_menu. Однако я не могу построить этот запрос для получения этих регистров. Я строю схему по этой ссылке: http://sqlfiddle.com/#!9/5ab86b

Ниже я показываю ожидаемый результат.

компании

ID	Имя
1	компания 1
2	компания 2
3	компания 3

пункты меню

ID	Имя
1	Главная
2	Диаграммы
3	Пользователи
4	Проекты

company_menu

ID	идентификатор компании	menu_item_id
1	1	1
2	1	2
3	1	3
4	1	4
5	2	1
6	2	3

Это результат, которого я ожидал:

ID	идентификатор компании	menu_item_id
1	2	2
2	2	4
3	3	1
4	3	2
5	3	3
6	3	4

 CREATE TABLE companies(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
 );
 
CREATE TABLE menu_items(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
 );
 
CREATE TABLE company_menu(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  company_id INT,
  menu_item_id INT,
  FOREIGN KEY(company_id) REFERENCES companies(id),
  FOREIGN KEY(menu_item_id) REFERENCES menu_items(id)
);

INSERT INTO companies (name) VALUES ("Company 1"),("Company 2"),("Company 3");
INSERT INTO menu_items (name) VALUES ("home"),("charts"),("users"),("projects");
INSERT INTO company_menu (company_id, menu_item_id) VALUES (1, 1),(1, 2),(1,3),(1,4);
INSERT INTO company_menu (company_id, menu_item_id) VALUES (2, 1),(2,3);

1. Предоставление скрипки-это очень хорошо! Но не забудьте также включить необходимые DDL и DML в сам вопрос. Если по какой-либо причине скрипка выходит из строя, эта информация теряется, и вопрос бесполезен для будущих читателей.

2. Это не «поворот». При повороте будет получена таблица с 3 строками (компании) и 4 столбцами (меню). (Или наоборот.)

Ответ №1:

Я могу придумать два способа. Не знаю, что эффективнее. Оба начинаются с полного объединения компаний-menu_items, чтобы получить все возможные комбинации, а затем вырезать существующие:

WHERE NOT EXISTS

 select c.id company_id, m.id menu_item_id
from companies c
join menu_items m
where not exists (
  select * from company_menu where company_id = c.id and menu_item_id = m.id
);

LEFT JOIN IS NULL :

 select c.id company_id, m.id menu_item_id
from companies c
join menu_items m
left join company_menu cm on cm.company_id = c.id and cm.menu_item_id = m.id
where cm.id is null;

И то, и другое можно сортировать в любом столбце company или menu_item.

http://sqlfiddle.com/#!9/5ab86b/11