Discussion :

[aminlove88]

Slt à tous,

je fais actuellement une base de donnée et j'essaye de la gérer du mieux possible.
Dans mon scenario, je souhaite attribuer à un utilisateur le droit de lire uniquement à une vue, histoire de réstreindre l'acces et qu'il ne puisse pas voir les autres infos dans les autre tables.
C'est dans la création de ma vue que je bloque ...
je souhaite donc avant même de la transformer en vue, réussir à faire ma requête.
En gros je désire fusionner deux requêtes(deux count) pour n'en faire qu'une. Voici les deux requêtes séparemment qui marchent bien :

cette requête me permet de compter le nombre de poste dans chaque service

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT lib_service, count( p.num_poste ) nbrposte
FROM service s, poste p
WHERE s.id_service = p.id_service
GROUP BY 1

et celle ci me compte le nombre de logiciel installés dans chaque service(

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT lib_service, count( i.id_log ) nbrlog
FROM service s, poste p, installer i, logiciel l
WHERE s.id_service = p.id_service
AND p.num_poste = i.num_poste
AND i.id_log = l.id_log
GROUP BY 1

Une fois rassemblée, j'ai bien le resultat de mes deux count mais ont le même resultat. jpige plus rien
Si quelqu'un sait commen s'y prendre pour metter deux count dans une même requête...
Merci d'avance

[Waldar]

Qu'entendez-vous par fusionner vos requêtes ?

Avoir un résultat du type :
POSTE -- service1 -- 15
POSTE -- service2 -- 23
LOGICIEL -- service1 -- 151
LOGICIEL -- service2 -- 253

Ou alors un résultat du type :
service1 -- 15 -- 151
service2 -- 23 -- 253

[CinePhil]

Avec des jointures normalisées ce serait plus clair !

Voici les deux requêtes séparemment qui marchent bien :

On se demande comment avec une telle syntaxe !

cette requête me permet de compter le nombre de poste dans chaque service

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT lib_service, count( p.num_poste ) nbrposte
FROM service s, poste p
WHERE s.id_service = p.id_service
GROUP BY 1

GROUP BY 1 ? Pas banal !
La bonne écriture :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT s.lib_service, count( p.num_poste ) nbrposte
FROM service s
INNER JOIN poste p ON s.id_service = p.id_service
GROUP BY s.lib_service

et celle ci me compte le nombre de logiciel installés dans chaque service

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT lib_service, count( i.id_log ) nbrlog
FROM service s, poste p, installer i, logiciel l
WHERE s.id_service = p.id_service
AND p.num_poste = i.num_poste
AND i.id_log = l.id_log
GROUP BY 1

Ecriture normalisée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT s.lib_service, count( i.id_log ) nbrlog
FROM service s
INNER JOIN poste p ON s.id_service = p.id_service
  INNER JOIN installer i ON p.num_poste = i.num_poste
    INNER JOIN logiciel l ON i.id_log = l.id_log
GROUP BY s.lib_service

La requête qui rassemble les deux :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT s.lib_service, 
  count(DISTINCT p.num_poste) nbrposte, 
  count( i.id_log ) nbrlog
 FROM service s
INNER JOIN poste p ON s.id_service = p.id_service
  INNER JOIN installer i ON p.num_poste = i.num_poste
    INNER JOIN logiciel l ON i.id_log = l.id_log
 GROUP BY s.lib_service

[aminlove88]

merci pour votre aide.
Cinephil je ne connaissais pas la commande "INNER JOIN".
Et je t'assure que le "group by 1" est bien normalisé et marche sans probleme . J'ai repris ta requête est je l'ai modifié avec les commandes que je connais :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT s.lib_service, count(DISTINCT p.num_poste) nbrposte, count( i.id_log ) nbrlog
FROM service s, poste p, installer i, logiciel l
where s.id_service=p.id_service
and i.num_poste=p.num_poste
and l.id_log=i.id_log
group by 1;
 
+-------------+----------+--------+
| lib_service | nbrposte | nbrlog |
+-------------+----------+--------+
| comptabili          4     12 
| direction           1     3                   
| informatiq          2     5  
| ressource           3     8 
| stagiaires          3     9   |

Mon soucis ici c'est que je n'avais pas renseigné mon count avec DISTINCT qui est obligatoire étant donné que dans la table installer le poste n°1 par exemple est renseigné plusieurs fois dans la table.
Merci encore.

[CinePhil]

Cinephil je ne connaissais pas la commande "INNER JOIN".

Ben il faut s'y mettre parce que c'est la norme !

Et je t'assure que le "group by 1" est bien normalisé et marche sans probleme .

Et il représente quoi le 1 ? La première colonne du SELECT ?

J'ai repris ta requête est je l'ai modifié avec les commandes que je connais :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
mysql> SELECT s.lib_service, count(DISTINCT p.num_poste) nbrposte, count( i.id_log ) nbrlog
FROM service s, poste p, installer i, logiciel l
where s.id_service=p.id_service
and i.num_poste=p.num_poste
and l.id_log=i.id_log
group by 1;

Rhaaaa ! Quelle horreur !

Mon soucis ici c'est que je n'avais pas renseigné mon count avec DISTINCT qui est obligatoire étant donné que dans la table installer le poste n°1 par exemple est renseigné plusieurs fois dans la table.
Merci encore.

Tu as au moins compris ça ! Mais je t'incite encore fortement à écrire les jointures de manière normalisée.
Un article sur les jointures très instructif...
Voir notamment les chapitres II-B et III.

[Chtulus]

Slut CinePhil,

Effectivement le 1 correspond à l'ordre de la colonne énumérait après le SELECT.

[CinePhil]

C'est quand même plus explicite d'écrire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

GROUP BY NomColonne

Imagine que ta requête aie 12 colonnes en alternant les colonnes de regroupement et les colonnes calculées, tu es obligé de compter la position des colonnes à inclure dans le GROUP BY. Et bonjour le débogage plus tard !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

GROUP BY 1,5,8,10

Beurk !

[pacmann]

Salut !

Je me fais l'avocat du diable (perso, je n'ai jamais écrit "ORDER BY 1"...) :
Dans son cas, il n'y avait pas 12 expression mais 2 !
Et du coup, c'est rapide décrire "1"... c'est un peu comme écrire SELECT * : tant que c'est juste pour des tests et de la demo, c'est pas grave

(Par contre, je suis d'accord que c'est plus parlant avec un nom de colonne )

[aminlove88]

J'ai essayé de faire en vain hier une autre requête qui me permette dans une 3eme colonne, suite aux deux count deja abordés ci dessus , d'afficher le nombre de logiciels installés (table installer) en retirant tout les systemes d'exploitations. (vous me direz j'abuse un peu =) )

Mais faudrait déja que j'arive à la faire seule et c'est pas gagné
Voici la requête que j'utilise (désolé pour ceux qui suivent la norme, mais c'est pour moi plus rapide de faire comme ça, j'espere au moins que vous comprendrez ) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT s.lib_service, count( i.id_log ) nbrlog_sans_SE
FROM service s, poste p, installer i, logiciel l
WHERE s.id_service = p.id_service
AND i.num_poste = p.num_poste
AND l.id_log = i.id_log
AND i.id_log NOT IN (
SELECT id_log
FROM logiciel
WHERE id_log = '1'
AND id_log = '9'
AND id_log = '10' )
GROUP BY s.lib_service
 
les id_log 1, 9 et 10 correspondent aux SE dans ma table logiciel.

Au final je n'ai pas ce que je veux, il me réaffiche le nombre de logiciel.

J'ai remarqué aussi qu'en ne mettant qu'une seule condition dans le select imbriqué (par exemple id_log='1' et pas d'autres), il m'affichait bien le nombre de logiciel exepté celui que je retire du compte , mais il n'aime pas à priori qu'il y en ai 3.
Comprends pas...

Merci de votre patience

[Chtulus]

Bonjour,

J'ai pas tout suivit mais utilisez l'opérateur OR à la place des AND !

[CinePhil]

J'ai essayé de faire en vain hier une autre requête qui me permette dans une 3eme colonne, suite aux deux count deja abordés ci dessus , d'afficher le nombre de logiciels installés

Vu la requête on est revenu au nombre de logiciels par service apparemment ?

[quote]en retirant tout les systemes d'exploitations.

Mais faudrait déjà que j'arrive à la faire seule et c'est pas gagné
Voici la requête que j'utilise (désolé pour ceux qui suivent la norme, mais c'est pour moi plus rapide de faire comme ça, j'espère au moins que vous comprendrez ) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT s.lib_service, count( i.id_log ) nbrlog_sans_SE
FROM service s, poste p, installer i, logiciel l
WHERE s.id_service = p.id_service
AND i.num_poste = p.num_poste
AND l.id_log = i.id_log
AND i.id_log NOT IN (
SELECT id_log
FROM logiciel
WHERE id_log = '1'
AND id_log = '9'
AND id_log = '10' )
GROUP BY s.lib_service
 
les id_log 1, 9 et 10 correspondent aux SE dans ma table logiciel.

Si dans ta tête tu séparais bien ce qui relève de la jointure de ce qui relève de la restriction, ce serait plus facile d'écrire des requêtes qui fonctionnent !

Pourquoi aller chercher une sous-requête alors que les valeurs de la restriction sont des id utilisés comme clé étrangère dans la requête principale ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT s.lib_service, COUNT(i.id_log) AS nbre_sans_SE
FROM service s
INNER JOIN poste p ON s.id_service = p.id_service
  INNER JOIN installer i ON p.num_poste = i.num_poste
    INNER JOIN logiciel l ON i.id_log = l.id_log
WHERE i.id_log NOT IN (1, 9, 10)
GROUP BY s.lib_service

Au passage, j'espère que vos id_log ne sont pas de type char ou varchar comme le suggère votre clause WHERE ? Les ' sont inutiles pour les valeurs numériques.

N'est-elle pas plus simple et plus lisible ma requête ?

[aminlove88]

Mais quel ... je suis

Oui c'est vrai que j'ai tendance à me prendre la tête, mais pour te dire la verité c'est surtout dans l'optique de l'exam que je fait ça. Vu qu'il n'y a pas grand chose déja, qu'il sache au moins que je sais utilisé le select imbriqué .

Il me reste plus qu'a tout rassembler mais je crains que ça ne puisse pas marcher, du fait qu'il y ai deux colonnes (count) qui utilisent le même champ.

Si je souhaite mettre une condition sur seulement un des deux count dans mon select(comme j'ai fait dans la precedente requête grâce à Chtulus qui compte les log sans SE) ce sera sans doute pas possible que l'autre (le count qui compte entierement les log installés) ne prenne pas en consideration la condition.
A moins q'un UNION ou INTERSECT puisse m'arranger ça.
Je ne crois pas qu'on puisse utiliser un alias du count pour appliquer specialement pour lui une condition (having nbrlog_ not in ( )???)
Qu'est ce que vous en pensez ?

merci

[CinePhil]

HAVING est une restriction qui s'opère sur le résultat des calculs donc sur le comptage (COUNT) et pas sur ce que ça compte (id_log).
On voit bien dans MA requête ci-dessus que la restriction sur les id_log est examinée pour toutes les lignes. Donc les colonnes sélectionnées et les calculs faits tiendront compte de cette restriction.
Pour compter une fois avec SE et une fois sans SE, il faut deux requêtes ou une union de deux requêtes.

[aminlove88]

L'utilisation de l'union permet d'afficher les valeures dictinctes des deux requêtes, donc je ne sais pas si j'aurais 3 colonnes à l'issue de cette commande.

j'ai essayé de la faire mais j'ai des erreurs, la voici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SELECT s.lib_service, count(DISTINCT p.num_poste) nbrposte, count(i.id_log) as nbrlog
FROM service s, poste p, installer i, logiciel l
WHERE s.id_service = p.id_service
AND i.num_poste = p.num_poste
AND l.id_log = i.id_log
 
union
 
SELECT s.lib_service, count( i.id_log ) as nbrlog_sans_SE
FROM service s, poste p, installer i, logiciel l
WHERE s.id_service = p.id_service
AND i.num_poste = p.num_poste
AND l.id_log = i.id_log
and i.id_log NOT IN (1,9,10)
group by s.lib_service

Et mon erreur generé par mysql est :
"#1140 - Mélanger les colonnes GROUP (MIN(),MAX(),COUNT()...) avec des colonnes normales est interdit s'il n'y a pas de clause GROUP BY."

je vais pas faire long feu =)

[CinePhil]

L'union de deux requêtes, c'est l'union de DEUX requêtes !
Pourquoi avoir retiré le GROUP BY de la première requête ?

Par contre, ce qui risque de ne pas fonctionner c'est que les colonnes ne sont pas les mêmes dans les deux requêtes. Il faut donc peut-être les simuler. Ce qui donnerait, AVEC DES JOINTURES NORMALISEES (Rogntudjuuu ! ):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT s.lib_service, COUNT(i.id_log) AS nbre_sans_SE, NULL AS nbrposte, NULL AS nbrlog
FROM service s
INNER JOIN poste p ON s.id_service = p.id_service
  INNER JOIN installer i ON p.num_poste = i.num_poste
    INNER JOIN logiciel l ON i.id_log = l.id_log
WHERE i.id_log NOT IN (1, 9, 10)
GROUP BY s.lib_service
 
UNION
 
SELECT s.lib_service, NULL AS nbre_sans_SE
  count(DISTINCT p.num_poste) nbrposte, 
  count( i.id_log ) nbrlog
 FROM service s
INNER JOIN poste p ON s.id_service = p.id_service
  INNER JOIN installer i ON p.num_poste = i.num_poste
    INNER JOIN logiciel l ON i.id_log = l.id_log
 GROUP BY s.lib_service

Par contre je pense que ça donnera deux lignes par service.

Une autre amélioration consiste alors à faire des deux premières requêtes des sous requêtes dans une troisième :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT T1.lib_service, T1.nbrposte, T2.nbrlog, T2.nbre_sans_SE
FROM (
  SELECT s.lib_service,
    count(DISTINCT p.num_poste) nbrposte, 
    count( i.id_log ) nbrlog
  FROM service s
   INNER JOIN poste p ON s.id_service = p.id_service
     INNER JOIN installer i ON p.num_poste = i.num_poste
       INNER JOIN logiciel l ON i.id_log = l.id_log
  GROUP BY s.lib_service
) T1
INNER JOIN (
  SELECT s.lib_service, COUNT(i.id_log) AS nbre_sans_SE, 
  FROM service s
  INNER JOIN poste p ON s.id_service = p.id_service
    INNER JOIN installer i ON p.num_poste = i.num_poste
      INNER JOIN logiciel l ON i.id_log = l.id_log
  WHERE i.id_log NOT IN (1, 9, 10)
  GROUP BY s.lib_service
) T2 ON T1.lib_service = T2.lib_service
ORDER BY T1.lib_service

[aminlove88]

balèse la requête
Il faut definitivement que je mette à la norme donc, c'est pourtant pas ces commandes qu'on nous donne à apprendre dans ma ptite formation d'info gestion.
j'essaierais lundi de poser la requête que tu viens de me donner CinePhil et tenter de comprendre déja comment cela a été posé.
J'ai pas l'occasion de faire ça ce week end pour vous dire si ca marche ou non, je ne serais pas chez moi.
Je vous tiens donc au courant très prochainement, merci.


sifflote °*°*°*On va finir par s'entendre maudite norme °*°*°*

[aminlove88]

Merci à toi Cinephil

Je viens de la tester, elle marche impeccable.
Mais il y a quelques petites manip que je n'ai pas saisi et souhaiterai que tu m'eclaire un peu plus :

tu affectes en fait la premiere requête(nbrposte+nbrlog) à une table virtuel (T1), et la deuxieme à T2 si j'ai bien compris. Pour ensuite faire la jointure logique de ces deux tables , est-ce bien cela ?
***demande confirmation ***

Je ne connaissais pas encore l'etendue de l'exploitation de SQL qui offre beaucoups plus de possibilités que je croyais, sympa =) .

Voici la requête un peu modifié, avec la norme attention :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT T1.lib_service, T1.nbrposte, T1.nbrlog, T2.nbre_sans_SE
FROM (
  SELECT s.lib_service,
    count(DISTINCT p.num_poste) nbrposte, 
    count( i.id_log ) nbrlog
  FROM service s
  INNER JOIN poste p ON s.id_service = p.id_service
  INNER JOIN installer i ON p.num_poste = i.num_poste
  INNER JOIN logiciel l ON i.id_log = l.id_log
  GROUP BY s.lib_service
) T1,   ( SELECT s.lib_service, COUNT(i.id_log) AS nbre_sans_SE
  FROM service s
  INNER JOIN poste p ON s.id_service = p.id_service
  INNER JOIN installer i ON p.num_poste = i.num_poste
  INNER JOIN logiciel l ON i.id_log = l.id_log
  where i.id_log NOT IN (1, 9, 10)
  GROUP BY s.lib_service ) T2
where T1.lib_service = T2.lib_service
ORDER BY T1.lib_service

ce qui donne :
+-------------+----------+--------+--------------+
| lib_service | nbrposte | nbrlog | nbre_sans_SE |
+-------------+----------+--------+--------------+
| comptabili | 4 | 12 | 8 |
| direction | 1 | 3 | 2 |
| informatiq | 2 | 6 | 4 |
| ressources | 3 | 9 | 6 |
| stagiaires | 3 | 10 | 7 |
+-------------+----------+--------+--------------+
Il ne me reste plus qu'a la transformer en vue et donner aux nouveaux users le droit de lire simplement sur celle ci.

[CinePhil]

tu affectes en fait la premiere requête(nbrposte+nbrlog) à une table virtuel (T1), et la deuxieme à T2 si j'ai bien compris. Pour ensuite faire la jointure logique de ces deux tables , est-ce bien cela ?
***demande confirmation ***

Oui c'est ça en gros. On joint les deux sous-requêtes.

Voici la requête un peu modifié, avec la norme attention :

Ben non ! Tu t'obstines à faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT quelquechose
FROM table1, table2
WHERE conditiondejointure

Au lieu de :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT quelquechose
FROM table1
INNER (ou LEFT OUTER ou RIGHT OUTER...) JOIN table2 ON conditiondejointure

Là tu as mélangé l'écriture normalisée dans les sous requêtes avec la non normalisée pour la requête globale !

[aminlove88]

Decidemment même la vue me fait défaut.

Mysql me retourne cette erreur :
erreur 1349 :View's SELECT contains a subquery in the FROM clause

Avec la même requête(mise à part le create view) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE VIEW V_nbrlog AS( SELECT T1.lib_service, T1.nbrposte, T1.nbrlog, T2.nbre_sans_SE
FROM (
  SELECT s.lib_service,
    count(DISTINCT p.num_poste) nbrposte, 
    count( i.id_log ) nbrlog
  FROM service s
  INNER JOIN poste p ON s.id_service = p.id_service
  INNER JOIN installer i ON p.num_poste = i.num_poste
  INNER JOIN logiciel l ON i.id_log = l.id_log
  GROUP BY s.lib_service) 
T1, ( SELECT s.lib_service, COUNT(i.id_log) AS nbre_sans_SE
        FROM service s
        INNER JOIN poste p ON s.id_service = p.id_service
        INNER JOIN installer i ON p.num_poste = i.num_poste
        INNER JOIN logiciel l ON i.id_log = l.id_log
        where i.id_log NOT IN (1, 9, 10)
        GROUP BY s.lib_service ) T2
where T1.lib_service = T2.lib_service
ORDER BY T1.lib_service )

Ce n'est donc pas compatible de mettre de requête à l'interieur d'un from dans la création d'une vue ?

[SQLpro]

Si, mais c'est sans doute une limitation propre à MySQL... En tout cas cela marche parfaitement avec SQL Server, Oracle ou IBM DB2.

Cependant tu peut encore t'en tirer avec une double vue... A moins que MySQL ne supporte pas cela non plus...

Alors reste la CTE pour ce faire, mais là je crois que MySQL ne l'accepte pas du tout...

Ce que c'est que de travailler avec les mauvais outils !!!

A +