Forum d'entraide PHPFrance

bonsoir

J'ai besoin d'un coup de main en SQL pour m'aider à faire une requête tordue

Voila, j'ai 3 tables :

- Asso : idasso, nom, tel...

- communes : idcom, commune

La première table contient une liste d'associations.

La seconde contient la liste des communes du département.

Une commune peut être "couverte" par un ou plusieurs association, j'ai donc créé la troisième table intercalaire :

- commune_asso : idcom, idasso

pour tous les usages normaux, ça marche impec.

Mais je voudrais faire une récap' avec :

- en ligne, la liste des communes (675),
- en colone, les asso (je n'en ai que 9),
et en valeurs, 0 ou 1 (un COUNT quoi).

Mais... je sèche, je ne vois pas comment faire ça.

Quelqu'un aurait une idée ?

Merci d'avance

doudi
[/b]

j'ai créé une copie de ma base dans Access, car j'avais déjà fait des trucs pareil dans access il y a quelques temps.... Et access genere du SQL

Voila ce qu'il me genere :

TRANSFORM Count(commune_ai.idai) AS CompteDeidai
SELECT communes.idcom, communes.commune
FROM communes LEFT JOIN (commune_ai LEFT JOIN ai ON commune_ai.idai = ai.idai) ON communes.idcom = commune_ai.idcom
GROUP BY communes.idcom, communes.commune
PIVOT ai.association;

Manque de pot, le copier-coller de ce code dans Mysql5 ne marche pas

=> #1064 - You have an error in your SQL syntax; bla bla bla...

Dans le manuel Mysql, je ne trouve rien pour m'aider... Au s'cours !!!!

(et merci d'avance

)

Le problème de SQL c'est qu'il ne peut sortir que ce qui existe. Donc si l'assocation X n'existe pas dans la ville Y (c'est-à-dire que l'enregistrement (X,Y) n'existe pas dans la table commune_asso), SQL ne va pas pouvoir l'inventer (c'est une conséquence du produit cartésien utilisé pour les jointures).
Ce que tu peux faire avec du SQL normal, c'est ça

Code : Tout sélectionner

SELECT count( a.idasso ) as cpte, a.nom, c.nom
FROM asso a, commune c, communeasso z
WHERE a.idasso = z.idasso
AND z.idcommune = c.idcommune
GROUP BY a.nom, c.nom

ça va te donner la liste des valeurs 1 de ton tableau, mais pas des valeurs 0

Pour avoir la liste de tous les couples associations/villes, il faut donc que tu supprimes la jointure et donc effectuer le produit cartésien de toutes les communes par toutes les associations sans te préoccuper de savoir si une association est dans une ville ou pas.

Code : Tout sélectionner

SELECT count( a.idasso ) as cpte, a.nom, c.nom
FROM asso a, commune c, communeasso z
GROUP BY a.nom, c.nom

Bien sûr, cela va donner un résultat aberrant dans les valeurs, mais au moins tu as tous les couples (associations/communes).

Peut-on avoir le beurre et l'argent du beurre ?

Oui. Dans certaines versions de bases de données, on peut ajouter des fonctions procédurales dans du code SQL. La syntaxe suivante marche dans MySQL 5 (peut-être avec la 4) et avec Oracle (syntaxe identique en enlevant le END).

Code : Tout sélectionner

SELECT count(CASE WHEN (a.idasso = z.idasso AND z.idcommune = c.idcommune)
                  THEN a.idasso
                  ELSE NULL
                  END) AS cpte, a.nom, c.nom
FROM asso a, commune c, communeasso z
GROUP BY a.nom, c.nom

La clause Where de la première requête a été déplacée dans le count ()
Tu récupères donc un tableau de toutes les associations/villes avec 1 si l'association existe et 0 si elle n'existe pas dans la ville. Après tu n'as plus qu'à lire ce résultat et en faire un tableau PHP.

Mais attention au temps de réponse : comme on a supprimé le WHERE, la base parcourt donc maintenant le nombre d'enregistrements de la table asso fois le nombre d'enregistrement de la table communeasso fois le nombre d'enregistrement de la table commune !

Je suis sur le cul !!!
Trop trop fort, je suis impressionné par tant de savoir

Super grand merci Caroube, ça c'est un exposé comme je les aime, clair, détaillé, avec des alternative, t'es trop fort

Je viens de faire des tests... C'est vrai qu'aucun résultat n'est très concluant, soit en terme de rendu, soit en terme de temps de réponse...

J'ai adapté les requêtes à ma base, elles fonctionnent toutes, reste à trouver le meilleur compromis.

C'est vraiment ballot que mysql (même en version 5) n'ait pas la puissance de produits bureautiques comme Access (j'ai utilisé access de la v.2 à la version 97 et déjà à l'époque ce principe de requete analyse croisée existait)... Enfin, on va faire avec ce qu'on a, et c'est déjà génial.

Encore merci pour tout

A+

doudi

Je dis ça rapidement, mais il y a une fonction native dans MySQL 5 qui mériterait une petite exploration : IFNULL() pour optimiser un peu ça en combinaisons avec des jointures externes au lieu de clauses WHERE

Je viens de faire des tests... C'est vrai qu'aucun résultat n'est très concluant, soit en terme de rendu, soit en terme de temps de réponse...

Je ne sais pas à quel rythme évolue le nombre d'associations par ville, mais à priori, ce n'est pas toutes les 10 minutes.
Ce que tu peux donc faire, c'est à chaque fois que tu ajoutes/supprimes une association, tu génères le code HTML du tableau. OK, ça prend un peu de temps, mais ce n'est fait qu'une seule fois par ajout/suppression et l'administrateur qui fait ça sait qu'il faut attendre un peu.

Mais après, pour les utilisateurs, il suffit juste d'afficher le HTML généré, ce qui est quasi-instantané.

C'est vraiment ballot que mysql (même en version 5) n'ait pas la puissance de produits bureautiques comme Access

Oui. Cette notion de PIVOT introduite dans les bases de données Microsoft n'est pas standard (mais le SQL n'est jamais standard). Elle vient tout juste d'apparaître (sous une forme différente) dans Oracle 11g. Et donc, pour faire des pivots dans les bases qui n'ont pas cet élément syntaxique, il n'y a pas d'autre solution que de jouer avec les CASE ou les IF dans le SQL

Modération :

Sujet splité. Pour ceux qui veulent suivre le débat, de manière constructive, c'est pas => ICI <=

Forum d'entraide PHPFrance

Requete analyse croisée

Requete analyse croisée