복잡한 조인이 있는 다른 테이블의 행 가져오기

복잡한 조인이 있는 다른 테이블의 행 가져오기

4개의 테이블이 있습니다(이름은 더 잘 이해하기 위해 괄호 안에 이탈리아어와 영어로 표시됨).

중요!:데이터베이스는 MariaDB-10.8입니다.

파사기(단계):

CREATE TABLE `passaggi` (
  `id` int(11) NOT NULL,
  `tipo_procedura_id` int(11) DEFAULT NULL,
  `passaggio` varchar(255) NOT NULL,
  `ordine` int(11) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

INSERT INTO `passaggi` (`id`, `tipo_procedura_id`, `passaggio`, `ordine`) VALUES
(1, 6, 'Attivazione', 1),
(2, 4, 'Attivazione', 1),
(3, 4, 'Assegno', 2),
(4, 3, 'Attivazione', 1),
(5, 3, 'Bando', 2),
(6, 3, 'Decreto di nomina della commissione', 3),
(7, 5, 'Attivazione', 1),
(8, 7, 'Attivazione', 1),
(9, 10, 'Attivazione', 1),
(10, 10, 'Avviso selezione', 2),
(11, 10, 'Decreto nomina commissione', 3),
(12, 10, 'Decreto approvazione atti', 4),
(13, 10, 'Decreto conferimento incarico', 5),
(14, 10, 'Inizio attività', 6),
(15, 10, 'Contratto', 7),
(16, 7, 'Secondo', 2);

Passaggi salvati(저장된 단계)

CREATE TABLE `passaggi_salvati` (
  `id` int(11) NOT NULL,
  `passaggio_id` int(11) NOT NULL,
  `data_documento` datetime NOT NULL,
  `procedura_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

INSERT INTO `passaggi_salvati` (`id`, `passaggio_id`, `data_documento`, `procedura_id`) VALUES
(5, 8, '2023-04-26 00:00:00', 4),
(6, 8, '2023-04-26 00:00:00', 5),
(7, 16, '2023-04-27 00:00:00', 5);

절차 복구(저장된 절차)

CREATE TABLE `procedure_salvate` (
  `id` int(11) NOT NULL,
  `tipo_procedura_id` int(11) DEFAULT NULL,
  `numero_procedura` varchar(255) DEFAULT NULL,
  `anno_procedura` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

INSERT INTO `procedure_salvate` (`id`, `tipo_procedura_id`, `numero_procedura`, `anno_procedura`) VALUES
(4, 7, '1', 2023),
(5, 7, '2', 2023);

Tipi 프로시저(프로시저 유형)

CREATE TABLE `tipi_procedura` (
  `id` int(11) NOT NULL,
  `procedura` varchar(255) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

INSERT INTO `tipi_procedura` (`id`, `procedura`, `parent_id`) VALUES
(1, 'Attivazione borse/assegni', NULL),
(2, 'Lettera di incarico per docenza', NULL),
(3, 'Borsa di studio per attività di ricerca', 1),
(4, 'Assegno di ricerca', 1),
(5, 'Corso di perfezionamento', 2),
(6, 'Master', 2),
(7, 'Seminari', 2),
(9, 'Contratti di collaborazione', NULL),
(10, 'Incarico individuale di prestazione professionale', 9);

저장된 절차, 단계, 절차 유형 및 저장된 단계가 있으면 NULL에서 데이터를 가져오는 쿼리를 작성하려고 합니다.

예를 들어, 위의 데이터와 필터가 설정된 경우procedure_salvate.id4 나는 이런 테이블을 원합니다.

numero_numorura	anno_suura	data_messo	pas_sal.id	파사조	프로시저	tipo_multura_id	pas.id
1	2023	2023-04-26	5	아티바치오네	세미나리	7	8
1	2023	NULL	NULL	세컨도	세미나리	7	16

그런 다음 필터를 사용합니다.procedure_salvate.id5 다음과 같은 테이블을 원합니다.

numero_numorura	anno_suura	data_messo	pas_sal.id	파사조	프로시저	tipo_multura_id	pas.id
2	2023	2023-04-26	6	아티바치오네	세미나리	7	8
2	2023	2023-04-27	7	세컨도	세미나리	7	16

이제, 저는 이 복잡하고 반복적인 쿼리로 이 결과를 얻습니다(나는 떨어집니다.ordine주문 목적으로만 사용되므로 위의 표에서):

SELECT `numero_procedura`,`anno_procedura`,`data_documento`,`pas_sal`.`id` AS `pas_sal.id`,`pas`.`passaggio`,`procedura`,`proc_sal`.`tipo_procedura_id`,`pas`.`id` AS `pas.id`, `ordine`
FROM `html_cf_modulistica_procedure_salvate` AS `proc_sal`
JOIN `html_cf_modulistica_passaggi_salvati` AS `pas_sal` ON `pas_sal`.`procedura_id` = `proc_sal`.`id`
JOIN `html_cf_modulistica_passaggi` AS `pas` ON `pas_sal`.`passaggio_id` = `pas`.`id`
JOIN `html_cf_modulistica_tipi_procedura` AS `proc` ON `proc_sal`.`tipo_procedura_id` = `proc`.`id`
WHERE `proc_sal`.`id` = 5
UNION
SELECT `numero_procedura`,`anno_procedura`,NULL AS `data_documento`,NULL AS `pas_sal.id`,`pas`.`passaggio`,`procedura`,`proc_sal`.`tipo_procedura_id`,`pas`.`id` AS `pas.id`, `ordine`
FROM `html_cf_modulistica_procedure_salvate` AS `proc_sal`
JOIN `html_cf_modulistica_passaggi` AS `pas` ON `proc_sal`.`tipo_procedura_id` = `pas`.`tipo_procedura_id`
JOIN `html_cf_modulistica_tipi_procedura` AS `proc` ON `proc_sal`.`tipo_procedura_id` = `proc`.`id`
WHERE `proc_sal`.`id` = 5 AND pas.id NOT IN(SELECT `passaggio_id` FROM `html_cf_modulistica_passaggi_salvati` WHERE `procedura_id` = 5)
ORDER BY `ordine`

제 목표는 좀 더 단순한 질문을 하는 것입니다.

잘 부탁드립니다.

항상 passagi의 모든 단계를 원하는 경우 이 테이블과 교차 조인한 후 왼쪽 join passagi_salvati:

SELECT `numero_procedura`,`anno_procedura`,`data_documento`,`pas_sal`.`id` AS `pas_sal.id`,
       `pas`.`passaggio`,`procedura`,`proc_sal`.`tipo_procedura_id`,`pas`.`id` AS `pas.id`, 
       `ordine`
FROM `procedure_salvate`      AS `proc_sal`
JOIN `tipi_procedura`         AS `proc`    ON `proc_sal`.`tipo_procedura_id` = `proc`.`id`
JOIN `passaggi`               AS `pas`     ON 1 = 1
LEFT JOIN `passaggi_salvati`  AS `pas_sal` ON `pas_sal`.`procedura_id` = `proc_sal`.`id`
                                          AND `pas_sal`.`passaggio_id` = `pas`.`id`
where `proc_sal`.`id` = 4 

dbfidle 데모

저는 4번과 5번 절차에 대한 테스트를 했고 결과는 당신의 질문과 일치합니다.

답을 알 수 있습니다.

SELECT `numero_procedura`,`anno_procedura`,`data_documento`,`pas_sal`.`id` AS `pas_sal.id`,`pas`.`passaggio`,`procedura`,`proc_sal`.`tipo_procedura_id`,`pas`.`id` AS `pas.id`, `ordine`
FROM `html_cf_modulistica_procedure_salvate`      AS `proc_sal`
JOIN `html_cf_modulistica_tipi_procedura`         AS `proc`    ON `proc_sal`.`tipo_procedura_id` = `proc`.`id`
join `html_cf_modulistica_passaggi`               AS `pas`     ON `proc_sal`.`tipo_procedura_id` = `pas`.`tipo_procedura_id`
left JOIN `html_cf_modulistica_passaggi_salvati`  AS `pas_sal` 
   ON `pas_sal`.`procedura_id` = `proc_sal`.`id`
  and `pas_sal`.`passaggio_id` = `pas`.`id`
where `proc_sal`.`id` = 4 

언급URL : https://stackoverflow.com/questions/76128992/get-rows-from-different-tables-with-complex-joins