You are not logged in.
- Topics: Active | Unanswered
Announcement
Download last stable version of GLPI - What can you do for GLPI ? : Contribute
Pages: 1
#1 2010-03-11 07:19:45
- lee
- Member
- Registered: 2007-01-31
- Posts: 562
INNER JOIN OU LEFT JOIN résultat différent
Bonjour
GLPI 0.72.4 , Mandrake 2.6.31.12-desktop-1mnb
mysql-5.1.42-0.2mdv2010.0 ,php-mysql-5.3.1-0.3mdv2010.0
apache-base-2.2.14-1.4mdv2010.0
Il y a des resultats differents sur les criteres globaux de recherche
Cas tratié :6 Postes et 2 imprimantes
Recherche des postes avec imprimantes (and imprimantes ^) = 2 (OK)
Recherche des postes avec imprimantes (and imprimantes NULL) = 4 (OK)
Recherche des postes et (imprantes ^ ou Imprimantes NULL) = 2 (je m'attendai à 6)
SELECT glpi_computers.name AS ITEM_0, glpi_computers.ID AS ITEM_0_2, glpi_dropdown_state.name AS ITEM_1, glpi_computers.ID AS ITEM_2, GROUP_CONCAT( DISTINCT CONCAT(glpi_printers.name,'$$' ,glpi_printers.ID) SEPARATOR '$$$$') AS META_0, GROUP_CONCAT( DISTINCT CONCAT(glpi_printers.name,'$$' ,glpi_printers.ID) SEPARATOR '$$$$') AS META_1, glpi_computers.ID AS ID
-> FROM glpi_computers
-> LEFT JOIN glpi_dropdown_state ON (glpi_computers.state = glpi_dropdown_state.ID)
-> INNER JOIN glpi_connect_wire AS conn_print_3 ON (conn_print_3.end2=glpi_computers.ID AND conn_print_3.type='3')
-> INNER JOIN glpi_printers ON (conn_print_3.end1=glpi_printers.ID)
-> WHERE glpi_computers.deleted='0' AND glpi_computers.is_template='0' AND ( glpi_computers.FK_entities IN ('12') ) AND ( (glpi_printers.name LIKE '%' ) OR (glpi_printers.name IS NULL ) ) GROUP BY glpi_computers.ID
-> ORDER BY ITEM_0 ASC ;
+--------+----------+--------+--------+----------------------+----------------------+-----+
| ITEM_0 | ITEM_0_2 | ITEM_1 | ITEM_2 | META_0 | META_1 | ID |
+--------+----------+--------+--------+----------------------+----------------------+-----+
| POSTE4 | 874 | OK | 874 | HP P3005 PCL 6$$1106 | HP P3005 PCL 6$$1106 | 874 |
| POSTE5 | 872 | OK | 872 | HP P2015 $$1107 | HP P2015 $$1107 | 872 |
+--------+----------+--------+--------+----------------------+----------------------+-----+
2 rows in set (0.00 sec)
Recherche des postes et (imprantes Null ou Imprimantes ^) = 6 (OK)
SELECT glpi_computers.name AS ITEM_0, glpi_computers.ID AS ITEM_0_2, glpi_dropdown_state.name AS ITEM_1, glpi_computers.ID AS ITEM_2, GROUP_CONCAT( DISTINCT CONCAT(glpi_printers.name,'$$' ,glpi_printers.ID) SEPARATOR '$$$$') AS META_0, GROUP_CONCAT( DISTINCT CONCAT(glpi_printers.name,'$$' ,glpi_printers.ID) SEPARATOR '$$$$') AS META_1, glpi_computers.ID AS ID
-> FROM glpi_computers
-> LEFT JOIN glpi_dropdown_state ON (glpi_computers.state = glpi_dropdown_state.ID)
-> LEFT JOIN glpi_connect_wire AS conn_print_3 ON (conn_print_3.end2=glpi_computers.ID AND conn_print_3.type='3')
-> LEFT JOIN glpi_printers ON (conn_print_3.end1=glpi_printers.ID)
-> WHERE glpi_computers.deleted='0' AND glpi_computers.is_template='0' AND ( glpi_computers.FK_entities IN ('12') ) AND ( (glpi_printers.name IS NULL ) OR (glpi_printers.name LIKE '%' ) ) GROUP BY glpi_computers.ID
-> ORDER BY ITEM_0 ASC ;
+---------+----------+--------+--------+----------------------+----------------------+------+
| ITEM_0 | ITEM_0_2 | ITEM_1 | ITEM_2 | META_0 | META_1 | ID |
+---------+----------+--------+--------+----------------------+----------------------+------+
| POSTE1 | 871 | OK | 871 | NULL | NULL | 871 |
| POSTE2 | 873 | OK | 873 | NULL | NULL | 873 |
| POSTE3 | 875 | OK | 875 | NULL | NULL | 875 |
| POSTE4 | 874 | OK | 874 | HP P3005 PCL 6$$1106 | HP P3005 PCL 6$$1106 | 874 |
| POSTE5 | 872 | OK | 872 | HP P2015 $$1107 | HP P2015 $$1107 | 872 |
| SERVEUR | 1028 | OK | 1028 | NULL | NULL | 1028 |
+---------+----------+--------+--------+----------------------+----------------------+------+
6 rows in set (0.01 sec)
J'ai reexcuté la requete en changeant l'ordre ( (glpi_printers.name LIKE '%') OR (glpi_printers.name IS NULL ) ) GROUP BY glpi_computers.ID ORDER BY ITEM_0 ASC; et c'est OK j'ai les 6
Pourquoi le INNER JOIN ne donne pas le meme resultat que le LEFT JOIN ?
Maintenant j'ai renomé le POSTE4 en POSTE24 et fait la recherche avec le premier critere ^POSTE2
pour ne rechercher que les POSTE2* avec ou sans imprimantes ..
SELECT glpi_computers.name AS ITEM_0, glpi_computers.ID AS ITEM_0_2, glpi_dropdown_state.name AS ITEM_1, glpi_computers.ID AS ITEM_2, GROUP_CONCAT( DISTINCT CONCAT(glpi_printers.name,'$$' ,glpi_printers.ID) SEPARATOR '$$$$') AS META_0, GROUP_CONCAT( DISTINCT CONCAT(glpi_printers.name,'$$' ,glpi_printers.ID) SEPARATOR '$$$$') AS META_1, glpi_computers.ID AS ID
-> FROM glpi_computers
-> LEFT JOIN glpi_dropdown_state ON (glpi_computers.state = glpi_dropdown_state.ID)
-> LEFT JOIN glpi_connect_wire AS conn_print_3 ON (conn_print_3.end2=glpi_computers.ID AND conn_print_3.type='3')
-> LEFT JOIN glpi_printers ON (conn_print_3.end1=glpi_printers.ID)
-> WHERE glpi_computers.deleted='0' AND glpi_computers.is_template='0' AND ( glpi_computers.FK_entities IN ('12') ) AND ( ( (glpi_computers.name LIKE 'POSTE2%' ) OR (glpi_dropdown_state.name LIKE 'POSTE2%' ) OR (glpi_computers.ID LIKE 'POSTE2%' ) ) AND (glpi_printers.name IS NULL ) OR (glpi_printers.name LIKE '%' ) ) GROUP BY glpi_computers.ID
-> ORDER BY ITEM_0 ASC ;
+---------+----------+--------+--------+----------------------+----------------------+-----+
| ITEM_0 | ITEM_0_2 | ITEM_1 | ITEM_2 | META_0 | META_1 | ID |
+---------+----------+--------+--------+----------------------+----------------------+-----+
| POSTE2 | 873 | OK | 873 | NULL | NULL | 873 |
| POSTE24 | 874 | OK | 874 | HP P3005 PCL 6$$1106 | HP P3005 PCL 6$$1106 | 874 |
| POSTE5 | 872 | OK | 872 | HP P2015 $$1107 | HP P2015 $$1107 | 872 |
+---------+----------+--------+--------+----------------------+----------------------+-----+
3 rows in set (0.00 sec)
J'ai le POSTE5 qui apparaît (meme resultat avec LIKE '%POSTE2%' )
Meme constat avec les imprimantes et les criteres globaux (NULL et ^) selon l'ordre et aussi pour la recherche d'une imprimante qui contient P2015 (pour le test j'ai cree une imprimante TEST P2015) et je ne recherche que les imprimantes P2015 avec ou sans ordinateurs , j'ai une imprimante en plus P3005
SELECT glpi_printers.FK_entities, glpi_printers.recursive, glpi_printers.name AS ITEM_0, glpi_printers.ID AS ITEM_0_2, glpi_entities.completename AS ITEM_1, glpi_entities.ID AS ITEM_1_2, glpi_printers.contact AS ITEM_2, glpi_infocoms.warranty_duration AS ITEM_3, GROUP_CONCAT( DISTINCT CONCAT(glpi_computers.name,'$$' ,glpi_computers.ID) SEPARATOR '$$$$') AS META_0, GROUP_CONCAT( DISTINCT CONCAT(glpi_computers.name,'$$' ,glpi_computers.ID) SEPARATOR '$$$$') AS META_1, glpi_printers.ID AS ID
-> FROM glpi_printers
-> LEFT JOIN glpi_entities ON (glpi_printers.FK_entities = glpi_entities.ID)
-> LEFT JOIN glpi_infocoms ON (glpi_printers.ID = glpi_infocoms.FK_device AND glpi_infocoms.device_type='3')
-> LEFT JOIN glpi_connect_wire AS conn_mon_1 ON (conn_mon_1.end1=glpi_printers.ID AND conn_mon_1.type='3')
-> LEFT JOIN glpi_computers ON (conn_mon_1.end2=glpi_computers.ID AND ( glpi_computers.FK_entities IN ('12') ) )
-> WHERE glpi_printers.deleted='0' AND glpi_printers.is_template='0' AND ( glpi_printers.FK_entities IN ('12') OR ( `glpi_printers`.`recursive`='1' AND `glpi_printers`.`FK_entities` IN ('0')) ) AND ( ( (glpi_printers.name LIKE '%P2015%' ) OR (glpi_entities.completename LIKE '%P2015%' ) OR (glpi_printers.contact LIKE '%P2015%' ) OR (glpi_infocoms.warranty_duration LIKE '%P2015%' ) ) AND (glpi_computers.name IS NULL ) OR (glpi_computers.name LIKE '%' ) ) GROUP BY glpi_printers.ID
-> ORDER BY ITEM_0 ASC ;
+-------------+-----------+----------------+----------+--------+----------+--------+--------+--------------+--------------+------+
| FK_entities | recursive | ITEM_0 | ITEM_0_2 | ITEM_1 | ITEM_1_2 | ITEM_2 | ITEM_3 | META_0 | META_1 | ID |
+-------------+-----------+----------------+----------+--------+----------+--------+--------+--------------+--------------+------+
| 12 | 0 | HP P2015 | 1107 | PRIMA | 12 | RESEAU | 12 | POSTE5$$872 | POSTE5$$872 | 1107 |
| 12 | 0 | HP P3005 PCL 6 | 1106 | PRIMA | 12 | RESEAU | 12 | POSTE24$$874 | POSTE24$$874 | 1106 |
| 12 | 0 | TEST P2015 | 2456 | PRIMA | 12 | | NULL | NULL | NULL | 2456 |
+-------------+-----------+----------------+----------+--------+----------+--------+--------+--------------+--------------+------+
3 rows in set (0.00 sec)
Si le test est fait sur une entité avec 200 imprimantes on se retrouve avec les 200 imprimantes (idem pour les ordinateurs)
Merci
Merci
GLPI 10.0.11/ Plugins GlpiInventory / AgentGlpi : 1.5 < Serveur Debian 64 Bits>
Offline
#2 2010-03-11 08:02:15
- remi
- GLPI-DEV
- From: Champagne
- Registered: 2007-04-28
- Posts: 7,127
- Website
Re: INNER JOIN OU LEFT JOIN résultat différent
Quand on recherche une valeur, la table (ici printers) est ajouté avec un INNER qui impose de trouver un résultat.
Quand on recherche NULL, la table est ajoutée avec un LEFT qui n'impose pas de trouver un résultat (puisque justement, on cherche l'absence de résultat)
Donc cela me semble tout à fait judicieux.
Effectivement le second critère utilise la table déjà liée, ce qui explique pourquoi le résultat est différent.
Suffit de le savoir.
+
Dév. Fedora 29 - PHP 5.6/7.0/7.1/7.2/7.3/7.4 - MariaDB 10.3 - GLPI master
Certifié ITILv3 - RPM pour Fedora, RHEL et CentOS sur https://blog.remirepo.net/
Offline
#3 2010-03-11 11:42:48
- lee
- Member
- Registered: 2007-01-31
- Posts: 562
Re: INNER JOIN OU LEFT JOIN résultat différent
Merci
mais comment chercher les postes POSTE2 (LIKE %POSTE2%) qui ont et n'ont pas des imprimantes
car si je mets le INNER il me donne que le POSTE2 (inprimante=^)
si je mets imprimante=NULL ou imprimante=^ (LEFT) il me donne POSTE2,POSTE24 mais aussi le POSTE5
Mais si j'ai 200 postes j'aurai les 200 postes affichés alors que je ne veux que les postes POSTE2
Merci
Merci
GLPI 10.0.11/ Plugins GlpiInventory / AgentGlpi : 1.5 < Serveur Debian 64 Bits>
Offline
#4 2010-03-11 11:59:55
- lee
- Member
- Registered: 2007-01-31
- Posts: 562
Re: INNER JOIN OU LEFT JOIN résultat différent
J'ai peut etre trouvé en ajoutant une parenthese ouvrante AND ( (glpi_printers.name IS NULL )
et bien sur l'autre OR (glpi_printers.name LIKE '%' ) ) ,jobtiens bien le resultat souhaité que les POSTE2
SELECT glpi_computers.name AS ITEM_0, glpi_computers.ID AS ITEM_0_2, glpi_dropdown_state.name AS ITEM_1, glpi_computers.ID AS ITEM_2, GROUP_CONCAT( DISTINCT CONCAT(glpi_printers.name,'$$' ,glpi_printers.ID) SEPARATOR '$$$$') AS META_0, GROUP_CONCAT( DISTINCT CONCAT(glpi_printers.name,'$$' ,glpi_printers.ID) SEPARATOR '$$$$') AS META_1, glpi_computers.ID AS ID FROM glpi_computers LEFT JOIN glpi_dropdown_state ON (glpi_computers.state = glpi_dropdown_state.ID) LEFT JOIN glpi_connect_wire AS conn_print_3 ON (conn_print_3.end2=glpi_computers.ID AND conn_print_3.type='3') LEFT JOIN glpi_printers ON (conn_print_3.end1=glpi_printers.ID) WHERE glpi_computers.deleted='0' AND glpi_computers.is_template='0' AND ( glpi_computers.FK_entities IN ('12') ) AND ( ( (glpi_computers.name LIKE '%POSTE2%' ) OR (glpi_dropdown_state.name LIKE '%POSTE2%' ) OR (glpi_computers.ID LIKE '%POSTE2%' ) ) AND ((glpi_printers.name IS NULL ) OR (glpi_printers.name LIKE '%' )) ) GROUP BY glpi_computers.ID ORDER BY ITEM_0 ASC;
+---------+----------+--------+--------+----------------------+----------------------+-----+
| ITEM_0 | ITEM_0_2 | ITEM_1 | ITEM_2 | META_0 | META_1 | ID |
+---------+----------+--------+--------+----------------------+----------------------+-----+
| POSTE2 | 873 | OK | 873 | NULL | NULL | 873 |
| POSTE24 | 874 | OK | 874 | HP P3005 PCL 6$$1106 | HP P3005 PCL 6$$1106 | 874 |
+---------+----------+--------+--------+----------------------+----------------------+-----+
2 rows in set (0.00 sec)
Confirmation que sans parentheses sur une autre entité ou il y a plus d'imprimantes et de postes j'obtiens 200 postes et avec parentheses le resultat souhaité 5 Postes
Last edited by lee (2010-03-12 05:40:52)
Merci
GLPI 10.0.11/ Plugins GlpiInventory / AgentGlpi : 1.5 < Serveur Debian 64 Bits>
Offline
Pages: 1