webdevpro.net

SQL – WHERE NOT EXISTS – est ce qu’une salle est disponible à la réservation ?

SQL – WHERE NOT EXISTS – est ce qu’une salle est disponible à la réservation ? | webdevpro.net

Introduction

Nous avons deux tables :

  • une qui contient une liste de salle de réunion
  • une autre qui contient la liste de toutes les réservations
  • elles sont reliées via un clé étrangère

La question est de savoir quelles salles de réunion sont libres à une date donnée ??? De préférence avec une seule requête SQL … Sans avoir à faire des boucles en PHP qui contiennent des requêtes SQL !

les tables et leur relation via une clé étrangère

Pour réaliser cette opération nous allons utiliser plusieurs concepts :

  1. deux requêtes :
    • liste de toutes les salles ( requête principale )
    • liste de toutes les salles réservées pour une date donnée ( sous requête )
  2. ces deux requêtes vont être imbriquées :
    • la requête principale dispose d’une clause WHERE
    • la sous-requête va alimenter la clause WHERE de la requête principale
  3. ceux deux requêtes vont être corrélées :
    • définition : une sous-requête corrélée est une sous-requête qui s’exécute pour chaque ligne de la requête principale et non une fois pour toute
    • la sous requête va être exécutée pour chaque valeur de la requête principale
    • Donc 1 : la sous requête va utiliser une variable de requête principale
    • Donc 2 : cette variable égalise une variable de la sous requête
  4. utilisation de l’opérateur EXISTS dans la requête principale
    • L’opérateur EXISTS est toujours associé à la clause WHERE et à une sous-requête
    • retourne TRUE si la sous requête renvoie une valeur
    • retourne FALSE si la sous requête aucune valeur

Pour rappel, le requête suivante ne renvoie rien :

SELECT * FROM table WHERE FALSE ;

La requête suivante est équivalente (si on considère qu’il n’y a aucun enregistrement ayant l’id 10 dans la tablea loc_agences ):

SELECT * FROM `loc_agences` WHERE EXISTS 
                       (SELECT * FROM `loc_agences` WHERE id = 10);

Tous les exemples ci dessous ont été réalisés sur MySQL

Structure et Jeu de données

--
-- Structure de la table `sallea_reservation_salle`
--

CREATE TABLE `sallea_reservation_salle` (
  `id` int(10) UNSIGNED NOT NULL,
  `salle_id` int(10) UNSIGNED NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Déchargement des données de la table `sallea_reservation_salle`
--

INSERT INTO `sallea_reservation_salle` (`id`, `salle_id`, `date`) VALUES
(1, 1, '2019-06-04'),
(2, 2, '2019-06-04'),
(3, 3, '2019-06-05'),
(4, 2, '2019-06-07');

-- --------------------------------------------------------

--
-- Structure de la table `sallea_salle`
--

CREATE TABLE `sallea_salle` (
  `id` int(10) UNSIGNED NOT NULL,
  `nom` varchar(60) NOT NULL,
  `ville` enum('Paris','Lyon') NOT NULL,
  `capacite` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Liste des salles de réunion à réservable';

--
-- Déchargement des données de la table `sallea_salle`
--

INSERT INTO `sallea_salle` (`id`, `nom`, `ville`, `capacite`) VALUES
(1, 'Salle 1 Paris', 'Paris', 6),
(2, 'Salle 2 Paris', 'Paris', 10),
(3, 'Salle 1 Lyon', 'Lyon', 10);

--
-- Index pour les tables déchargées
--

--
-- Index pour la table `sallea_reservation_salle`
--
ALTER TABLE `sallea_reservation_salle`
  ADD PRIMARY KEY (`id`),
  ADD KEY `salle_id` (`salle_id`);

--
-- Index pour la table `sallea_salle`
--
ALTER TABLE `sallea_salle`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT pour les tables déchargées
--

--
-- AUTO_INCREMENT pour la table `sallea_reservation_salle`
--
ALTER TABLE `sallea_reservation_salle`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT pour la table `sallea_salle`
--
ALTER TABLE `sallea_salle`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- Contraintes pour les tables déchargées
--

--
-- Contraintes pour la table `sallea_reservation_salle`
--
ALTER TABLE `sallea_reservation_salle`
  ADD CONSTRAINT `sallea_reservation_salle_ibfk_1` FOREIGN KEY (`salle_id`) REFERENCES `sallea_salle` (`id`);
COMMIT;

Jeu de données sous forme graphique

Table sallea_salle

id nom ville capacite
1 Salle 1 Paris Paris
 6
2 Salle 1 Paris Paris 10
3 Salle 1 Lyon Lyon 10

Table sallea_reservation_salle

id salle_id date
1 1 2019-06-04
2 2 2019-06-04
3 3 2019-06-05
4 2 2019-06-07

Requête SQL

Voici la requête à effectuer :

SET @dtresa = "2019-06-04" ;
SELECT s.nom FROM `sallea_salle` as s
WHERE NOT EXISTS
(SELECT * FROM sallea_reservation_salle as r
 WHERE 1 = 1 
 AND r.salle_id = s.id
 AND r.date = @dtresa)

Quelques remarques :

  1. La requête va retourner Salle 1 Lyon car effectivement le 2019-06-04 c’est la seule salle qui n’est pas réservée : qui n’a pas d’enregistrement dans la table sallea_reservation_salle
  2. La sous requête est bien corrélée avec la requête principale grâce à l’égalité : r.salle_id = s.id
  3. J’ai utilisé une variable SQL, SET @dtresa = "2019-06-04" pour facilité les tests sur d’autres dates

Ailleurs sur le net