skip to Main Content

I have 4 tables (names are in italian and english in parenthesis for better comprehension):

IMPORTANT!: Database is MariaDB-10.8

Passaggi (steps):

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 (saved steps)

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);

Procedure salvate (saved procedure)

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 procedura (procedure types)

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);

I want to write a query that take data from saved procedure, steps, procedure types and, if exists saved steps else NULL.

For example, with above data and a filter on procedure_salvate.id = 4 I want a table like this:

numero_procedura anno_procedura data_documento pas_sal.id passaggio procedura tipo_procedura_id pas.id
1 2023 2023-04-26 5 Attivazione Seminari 7 8
1 2023 NULL NULL Secondo Seminari 7 16

Then, with filter on procedure_salvate.id = 5 I want a table like this:

numero_procedura anno_procedura data_documento pas_sal.id passaggio procedura tipo_procedura_id pas.id
2 2023 2023-04-26 6 Attivazione Seminari 7 8
2 2023 2023-04-27 7 Secondo Seminari 7 16

Now, I get this result with this complex and repetitive query (I drop ordine from above table because it’s only for ordering purpose):

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`

My goal is to have a more simply query.

Thanks in advance

2

Answers


  1. Chosen as BEST ANSWER

    I get the answer:

    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 
    

  2. If you always want all steps from passagi then make a cross join with this table, then left join passaggi_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 
    

    dbfiddle demo

    I did tests for procedures 4 and 5 and results corresponds with your query.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search