Recherche d'un ensemble dans un autre ensemble

Recherche d'un ensemble dans un autre ensemble - SQL/NoSQL - Programmation

Marsh Posté le 20-12-2007 à 11:33:25    

Salut ! :hello:  
 
J'ai un p'tit problème que je n'arrive pas à résoudre : je dois chercher un ensemble dans un autre ensemble, j'ai essayé pleins de trucs mais sans succès.
Mon but est de résoudre ce problème en une seule requête SQL, sans utiliser de procédure stockée ou de code niveau PHP sinon c'est pas marrant. :D
 
Voici un exemple pour mieux comprendre :
 
Le schéma de test avec ses données :

Code :
  1. --  
  2. -- Structure de la table `salles_softs`
  3. --  
  4. CREATE TABLE `salles_softs` (
  5.  `num_salle` int(11) NOT NULL,
  6.  `num_logiciel` int(11) NOT NULL
  7. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  8. --  
  9. -- Contenu de la table `salles_softs`
  10. --  
  11. INSERT INTO `salles_softs` (`num_salle`, `num_logiciel`) VALUES (50, 1), (50, 2), (51, 3);
  12. -- --------------------------------------------------------
  13. --  
  14. -- Structure de la table `softs`
  15. --  
  16. CREATE TABLE `softs` (
  17.  `num_logiciel` int(11) NOT NULL
  18. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  19. --  
  20. -- Contenu de la table `softs`
  21. --  
  22. INSERT INTO `softs` (`num_logiciel`) VALUES (1), (2), (3);


 
Graphiquement :


mysql> select * from salles_softs;
+-----------+--------------+
| num_salle | num_logiciel |
+-----------+--------------+
|        50 |            1 |  
|        50 |            2 |  
|        51 |            3 |  
+-----------+--------------+
 
mysql> select * from softs;
+--------------+
| num_logiciel |
+--------------+
|            1 |  
|            2 |  
|            3 |  
+--------------+


 
Avez-vous une idée de la requête SQL qu'il faudrait écrire pour savoir si la salle 50 contient les logiciels 1 et 2 ? Ou encore plus compliqué : savoir quelles sont les salles qui contiennent les logiciels 1 et 2 ?
En lisant la question, ça paraît enfantin mais je bloque, c'est chiant :o
 
A votre bon cœur ! :jap:

Reply

Marsh Posté le 20-12-2007 à 11:33:25   

Reply

Marsh Posté le 20-12-2007 à 11:45:07    

pour savoir les salles qui ont le soft 1 et 2, après c'est juste une histoire d'adaptation
 

Code :
  1. select a.num_salle,count(*)
  2. from salles_softs a
  3. where a.num_logiciel in (1,2)
  4. group by a.num_salle
  5. having count(*) > 1

Reply

Marsh Posté le 20-12-2007 à 12:20:19    

J'avais pas pensé au COUNT, bien joué ;)
Y'a juste à modifier le HAVING pour vérifier que la somme égale le nombre de logiciels demandés et ça rulez.
 

Code :
  1. SELECT num_salle, COUNT(*) cnt FROM salles_softs WHERE num_logiciel IN (1,2) GROUP BY num_salle HAVING cnt = 2;


 
Autre façon intéressante basée sur un COUNT (:)) en utilisant les sous-requêtes corrélées :
 

Code :
  1. SELECT DISTINCT num_salle n FROM salles_softs WHERE (SELECT COUNT(*) FROM salles_softs WHERE num_salle = n AND num_logiciel IN (1,2)) = 2 ;


 
Si y'en a qui ont d'autres solutions pour résoudre ce type de problème, n'hésitez pas ! :)
 
Merci :hello:


Message édité par *syl* le 20-12-2007 à 12:20:54
Reply

Marsh Posté le 20-12-2007 à 13:33:50    

count(*) est potentiellement une mauvaise idée.
Si, par hasard, tu te retrouves avec un doublon comme ça:

+-----------+--------------+
| num_salle | num_logiciel |
+-----------+--------------+
|        50 |            1 |  
|        50 |            1 |  
|        51 |            3 |  
+-----------+--------------+


Ta requête va te remonter "vrai" alors que ce n'est pas ce que tu cherches.
Je remplacerais donc count(*) par count(distinct num_logiciel )

 


Message édité par anapajari le 20-12-2007 à 13:34:16
Reply

Marsh Posté le 20-12-2007 à 13:43:59    

Ouep, j'y avais pensé aux doublons mais ça c'est réglé par la compound primary key qui n'existe pas dans ce test mais en réalité si ! ;)

Reply

Marsh Posté le 20-12-2007 à 14:40:25    

Tiens, c'est marrant : en ajoutant les PK sur cet exemple, ma requête ne fonctionne plus :??:  
 


mysql> SELECT DISTINCT num_salle n FROM salles_softs WHERE (SELECT COUNT(*) FROM salles_softs WHERE num_salle = n AND num_logiciel IN (1,2)) = 2 ;
+----+
| n  |
+----+
| 50 |  
+----+
1 row in set (0.00 sec)
 
mysql> ALTER TABLE `salles_softs` ADD PRIMARY KEY  (`num_salle`,`num_logiciel`) ;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE `softs` ADD PRIMARY KEY (`num_logiciel`) ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> SELECT DISTINCT num_salle n FROM salles_softs WHERE (SELECT COUNT(*) FROM salles_softs WHERE num_salle = n AND num_logiciel IN (1,2)) = 2 ;
Empty set (0.00 sec)


 
Je décide donc de faire plus propre en ajoutant la table "salles" :

Code :
  1. --  
  2. -- Structure de la table `salles`
  3. --  
  4. CREATE TABLE `salles` (
  5.  `num_salle` int(11) NOT NULL,
  6.  PRIMARY KEY  (`num_salle`)
  7. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  8. --  
  9. -- Contenu de la table `salles`
  10. --  
  11. INSERT INTO `salles` (`num_salle`) VALUES (50), (51);


 
On ré-exécute la requête un peu modifiée pour utiliser cette table et là ça marche ce qui est normal cette fois ci..


mysql> SELECT num_salle n FROM salles WHERE (SELECT COUNT(*) FROM salles_softs WHERE num_salle = n AND num_logiciel IN (1,2)) = 2 ;
+----+
| n  |
+----+
| 50 |  
+----+
1 row in set (0.00 sec)


Bizarre, n'est-ce pas ? :/

Reply

Marsh Posté le 20-12-2007 à 15:20:21    

count(distinct num_logiciel)
 
ça devrait faire l'affaire. en tout cas, sous SQL Server 2005 ça marche nickel chrome.
 

Code :
  1. CREATE TABLE salles_softs
  2. (
  3.  salle_id int NOT NULL,
  4.  soft_id int NOT NULL
  5. );
  6.  
  7. CREATE INDEX ix_salles_softs ON salles_softs (salle_id, soft_id);
  8.  
  9. INSERT INTO salles_softs (salle_id, soft_id) VALUES (1, 1);
  10. INSERT INTO salles_softs (salle_id, soft_id) VALUES (1, 1);
  11. INSERT INTO salles_softs (salle_id, soft_id) VALUES (1, 3);
  12. INSERT INTO salles_softs (salle_id, soft_id) VALUES (2, 1);
  13. INSERT INTO salles_softs (salle_id, soft_id) VALUES (2, 3);
  14. INSERT INTO salles_softs (salle_id, soft_id) VALUES (3, 1);
  15. INSERT INTO salles_softs (salle_id, soft_id) VALUES (3, 1);
  16. INSERT INTO salles_softs (salle_id, soft_id) VALUES (3, 2);
  17. INSERT INTO salles_softs (salle_id, soft_id) VALUES (4, 1);
  18. INSERT INTO salles_softs (salle_id, soft_id) VALUES (4, 2);
  19. INSERT INTO salles_softs (salle_id, soft_id) VALUES (4, 3);
  20.  
  21. SELECT salle_id
  22. FROM salles_softs
  23. WHERE soft_id IN (1, 2)
  24. GROUP BY salle_id
  25. HAVING count(DISTINCT soft_id) = 2;
  26.  
  27. DROP TABLE salles_softs;


 


salle_id
-----------
3
4


Message édité par MagicBuzz le 20-12-2007 à 15:33:00
Reply

Marsh Posté le 20-12-2007 à 15:34:16    

Marche également parfaitement sous MySQL 5 :jap:

Reply

Marsh Posté le 30-05-2008 à 17:34:56    

Moi aussi j'ai un problème similaire :
 
Comment on fait pour dire qu'un sous-ensemble est présent dans un ensemble :
 
que le couple (1,2) est présent dans la liste de couple ((1,2),(1,3), etc.)
C'est comme un IN mais sur des ensembles, pas sur une simple valeur...et ça compile pas :/.


---------------
Asus P5Q Pro | C2D E8400 3GHz@4GHz + Noctua NH-C12P | 2x2Go Patriot Extreme PC-8500 | GeForce GTX 460@Stock 1Go GLH | Crucial SSD M4 64Go Sata3
Reply

Marsh Posté le 10-06-2008 à 22:51:33    

utilise une clause exists

Reply

Marsh Posté le 10-06-2008 à 22:51:33   

Reply

Marsh Posté le 17-07-2008 à 10:57:17    

HappyHarry a écrit :

utilise une clause exists


 
Parfaitement raison, j'avais déjà trouvé et ça s'applique très bien. Voici un exemple en JPQL :
 

...
and (  
        exists (select vcg.objetParametrable from ValeurCaracteristiqueCompte vcg where (vcc.valeur.ivalcaq=:p6_vcc_valeur_ivalcaq AND vcc.taffect>=:p7_vcc_taffect) and vcg.objetParametrable=vcc.objetParametrable)
        AND exists (select vcg.objetParametrable from ValeurCaracteristiqueCompte vcg where (vcc.valeur.ivalcaq=:p8_vcc_valeur_ivalcaq AND vcc.taffect<=:p9_vcc_taffect) and vcg.objetParametrable=vcc.objetParametrable)
        )
...


Message édité par Giz le 17-07-2008 à 10:57:58

---------------
Asus P5Q Pro | C2D E8400 3GHz@4GHz + Noctua NH-C12P | 2x2Go Patriot Extreme PC-8500 | GeForce GTX 460@Stock 1Go GLH | Crucial SSD M4 64Go Sata3
Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed