Problème requête avec jointures multiples [Résolu]

[babouu]

Bonjour à tous,


j'ai comme vous pouvez vous en douter un problème au niveau d'une requête SQL trop complexe pour mon tout petit niveau, et j'aurais grandement besoin de votre aide !


Voici le contexte : dans le cadre d'un dispositif de détection d'articles dans un rayonnage (en RFID), je dois créer un petit logiciel permettant de gérer les infos reçues...

Nous avons concrètement des articles (textile ici) répartis dans plusieurs rayons d'un magasin. Chaque article est attaché à une puce RFID, identifiée par un id unique.
Les différentes antennes RFID du dispositif vont scanner leur rayon respectif et récupérer les identifiants des puces RFID qu'ils auront captés.
Le dispositif communique ensuite cette liste de couples id_Rfid/id_Antenne à la base de données.

C'est ensuite à partir de cette base de données que le logiciel va afficher toutes les infos nécessaires et effectuer les traitements que l'utilisateur aurait besoin de faire.


Pour le moment j'ai la base de donnée que voici :



PS : j'utilise Visual Studio pour le logiciel et la base de donnée.


Nous avons donc les tables suivantes :

- article_detecte : contient l'ensemble des id_Rfid (uniques) des articles détectés par le dispositif, ainsi que identifiant de l'antenne qui les a détectés. En effet, il existe plusieurs

zones et donc plusieurs antennes RFID.

- tag : contient les associations entre les id_Rfid des tags et les id_Articles. Me sert à faire le lien entre un tag et l'article sur lequel il est attaché.

- article : c'est ma "base articles". Elle contient en fait toutes les désignations articles avec lesquels je travaille. id_Article identifie un article de façon unique et les autres champs

nous donnent diverses informations dont nous pouvons avoir besoin (dont l'emplacement théorique du produit, qui est très important).

- zone : définit les différentes zones de détection dans le magasin (ex : rayon 1, rayon 2, réserve, cabines d'essayage...)

- antene (avec une faute d'orthographe, oui désolé ) : contient les associations entre les id_Antenne et les id_Zone. Me sert à faire le lien entre une antenne et la zone dans laquelle elle

est installée.



Voilà, je pense que c'est à peu près complet...mais c'est maintenant que mon problème arrive !


Je souhaites créer une requête qui me donnerais l'ensemble des articles détectés avec leur emplacement réel et théorique ainsi que leur quantité (et avec diverses informations comme le

cug, le libelle, les seuils, etc...mais ça je saurais faire une fois le "gros" de la requête trouvé...).

Mais j'ai un gros souci : il me faut bien sur les quantités des articles là où on les a détectés réellement mais aussi les articles non détectés avec une quantité de 0 (zéro).

En effet, si un article (un tag) est référence mais non détecté, c'est qu'il est en rupture de stock. Il faut alors l'afficher à zéro !

Il y a donc une histoire de LEFT OUTER JOIN mixé avec des INNER JOIN, mais je n'y arrive pas du tout Je crois bien que c'est un peu trop compliqué pour mes compétences actuelles.





Voici le contenu des tables :

Code :

1
2
3
4
5
antene :
id_Antenne      id_Zone
1                   1
2                   2
3                   3

Code :

1
2
3
4
5
zone :
id_Zone         nom_Zone
1                  Rayon 1
2                  Rayon 2
3                  Rayon 3

Code :

1
2
3
4
5
6
7
article :
id_Article      id_Zone      Libelle                     seuilbas      seuilhaut      cug
1111            1               Tee-Shirt Blanc XL    2               8                111111
1112            1               Polo Vert S              2               8                111112
1113            2               Jean Noir L               2               5                111113
1114            3               Chemise Rouge L       3               7                111114
1115            3               Chemise Rouge XL     2               5                111114

Code :

1
2
3
4
5
6
7
8
tag :
id_Rfid                id_Article
222221               1111
222222               1112
222223               1113
222224               1113
222225               1113
222226               1114

Code :

1
2
3
4
5
6
7
article_detecte : (je n'affiche pas date et puissance qui ne servent à rien ici)
id_Rfid               id_Antenne
222221              1
222222              2
222223              2
222224              3
222225              2

J'ai donc besoin de pouvoir afficher l'ensemble des articles détectés, avec le cug, le libelle, l'emplacement, la quantité, les seuils, etc... et aussi les articles faisant parti de la

table "article" qui ne sont pas détectés (donc pas présents dans "article_detecte") en leur attribuant la quantité zéro pour signifier la rupture.

Après même si la quantité zéro n'est pas envisageable car infaisable avec un COUNT, il faudrait au moins avoir un signe distinctif pour ces articles en rupture (par exemple un champ à NULL

pour ces articles en question) pour que je puise faire un traitement dans le logiciel par la suite pour afficher la valeur zéro.


Voici à partir de ces tables ce que j'aimerais obtenir :

Code :

1
2
3
4
5
6
7
cug      libelle          Nom Rayon   id_Zone Réelle   id_Zone Théorique   seuilbas   seuilhaut   quantité
111114      Chemise Rouge L         NULL                    NULL                 3                    3           7            0
111114      Chemise Rouge XL       NULL                    NULL                 3                    2           5            0
111113      Jean Noir L                 Rayon 2                2                 2                    2           5            2
111113      Jean Noir L                 Rayon 3                3                 2                    2           5            1
111112      Polo Vert S                 Rayon 2                2                 1                    2           8            1
111111      Tee-Shirt Blanc XL       Rayon 1                1                 1                    2           8            1

Avez-vous une idée de la requête qui pourrait me donner ce résultat ? Je nage complètement actuellement, je suis paumé...




Merci d'avance à ceux qui voudront bien me donner un coup de main !


Bonne soirée

[babouu]

Oula désolé je ne pensait pas que la "présentation" des tables allait être modifiée...du coup tout est compacté avec seulement un espace entre les champs

Désolé pour ça, avez-vous un moyen d'afficher ces tables facilement et clairement ?


En espérant que ça ne gêne pas trop à la compréhension du problème.

Merci

[CinePhil]

Tu nous montres ce que tu as essayé comme requête ?

Si j'ai bien compris, tu veux tous les articles et les informations disponibles dans les autres tables quand elles existent. Donc tu auras un truc du genre :

Code :

1
2
FROM article
LEFT OUTER JOIN une_autre_table

La table article étant à gauche, tu auras tous les articles.

les articles faisant parti de la table "article" qui ne sont pas détectés (donc pas présents dans "article_detecte") en leur attribuant la quantité zéro pour signifier la rupture.

Les colonnes issues de la table de droite afficheront NULL. Pour remplacer ce NULL par un zéro, utilise

Code :

COALESCE(une_colonne, 0) AS un_nom_alias

[skuatamad]

Je pense que tu n'as besoin que d'un LEFT JOIN, le reste pouvant se faire en INNER JOIN via une sous-requête (ce qui est mieux pour les perfs)
Un bon début de réflexion :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT a.cug, a.libelle, tmp.nom_rayon, tmp.id_zone_reelle, a.id_Zone AS id_zone_theorique, 
       a.seuilbas, a.seuilhaut, coalesce(tmp.qte,0) AS quantite
  FROM article a
  LEFT JOIN (SELECT t.id_Article, z.nom_Zone AS nom_rayon, z.id_Zone AS id_zone_reelle, count(*) AS qte
               FROM tag t
               JOIN article_detecte ad ON ad.id_Rfid = t.id_Rfid
               JOIN antene a           ON a.id_Antenne = ad.id_Antenne
               JOIN zone z             ON z.id_Zone = a.id_Zone
              GROUP BY t.id_Article, z.nom_Zone, z.id_Zone
            ) tmp 
       ON tmp.id_Article = a.id_Article
 ORDER BY a.libelle
 
       CUG LIBELLE            NOM_RAY ID_ZONE_REELLE ID_ZONE_THEORIQUE   SEUILBAS  SEUILHAUT   QUANTITE
---------- ------------------ ------- -------------- ----------------- ---------- ---------- ----------
    111114 Chemise Rouge L                                           3          3          7          0
    111114 Chemise Rouge XL                                          3          2          5          0
    111113 Jean Noir L        Rayon 2              2                 2          2          5          2
    111113 Jean Noir L        Rayon 3              3                 2          2          5          1
    111112 Polo Vert S        Rayon 2              2                 1          2          8          1
    111111 Tee-Shirt Blanc XL Rayon 1              1                 1          2          8          1
 
6 rows selected.

En tous cas, très bon effort pour la description du besoin, c'est rare

[babouu]

Merci beaucoup pour vos réponses !

Mon problème est résolu, j'ai enfin réussi à obtenir les infos que je voulais même si je crois que ma requête n'est pas très académique et pas optimisée... Mais ça fonctionne

Pour info voici l'horreur qui me retourne le bon résultat (à part les quantités qui sont à 1, mais que je change par la suite dans le code du programme en c#) :

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT        article.cug AS Code, article.libelle AS Libelle, zone.nom_Zone AS Emplacement, COUNT(article.id_Article) AS Quantité, zone.id_Zone AS [Zone Réelle], article.id_Zone AS [Zone Théorique], article.seuil_bas AS [Seuil Bas], article.seuil_haut AS [Seuil Haut]
 
FROM            article LEFT OUTER JOIN
                         tag ON article.id_Article = tag.id_Article LEFT OUTER JOIN
                         article_detecte ON tag.id_Rfid = article_detecte.id_Rfid LEFT OUTER JOIN
                         antenne ON article_detecte.id_Antenne = antenne.id_Antenne LEFT OUTER JOIN
                         zone ON antenne.id_Zone = zone.id_Zone
 
GROUP BY article.libelle, article.cug, zone.nom_Zone, zone.id_Zone, article.id_Zone, article.seuil_bas, article.seuil_haut
 
ORDER BY Code, Libelle

Je continue les tests en faisant des modifications dans la base de donnée pour vérifier que c'est toujours cohérent, et pour le moment ça semble bon.

Merci encore !

[skuatamad]

Envoyé par babouu

Pour info voici l'horreur qui me retourne le bon résultat (à part les quantités qui sont à 1, mais que je change par la suite dans le code du programme en c#) :

C'est normal tu comptes sur id_article qui a une valeur, compte sur un colonne NULL pour pouvoir utiliser coalesce comme par exemple count(zone.id_Zone)
Par contre as tu testé ma requête, as tu vraiment besoin de mettre toutes les tables en OUTER JOIN ?