Discussion :

[hadeslabs]

Bonjour,
D'une manière générale, mes requêtes sont simples, or aujourd'hui,
je m'attaque à quelque chose qui me dépasse malgré quelques lectures.
J'ai quatre tables

Table articles : mk_piece_article
p_id, p_reference, p_designation, p_nb_actuel

Table commandes : mk_cdes
cde_id, nom, date

Table lignes de commande : mk_cde_lignes
cdeLigne_id, cde_id, p_id, qte, livraison

Table commentaire : mk_cde_pcomments
pcc_id, cde_id, p_id, cde_p_details

Certains articles commandés, ont un commentaire et
j'aimerai afficher les articles d'une commande avec leur commentaire respectif
si ils en possèdent.

Je ne sais pas comment m'y prendre.

Avec cette requête, j'ai le commentaire respectif des articles,
mais si un article de cette commande figure dans une autre commande,
il est également affiché alors qu'il ne fait pas parti de la commande n°4 de l'exemple ci-dessous.

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT cdeLigne_id,	Lcde.cde_id,	Lcde.p_id,	qte, livraison, p_reference, p_designation, p_machine, p_nb_actuel, cde_p_details 
FROM mk_cde_lignes AS Lcde 
INNER JOIN mk_piece_article AS p 
ON Lcde.p_id = p.p_id 
LEFT OUTER JOIN mk_cde_pcomments AS c 
ON  c.p_id = Lcde.p_id
WHERE Lcde.cde_id = 4 
ORDER BY p_reference;

Et avec celle-ci, j'ai exactement le bon nombre de ligne d'articles
mais tous mes articles ont le même commentaire, même ceux qui ne sont pas commentés

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT cdeLigne_id,	Lcde.cde_id,	Lcde.p_id,	qte, livraison, p_reference, p_designation, p_machine, p_nb_actuel, cde_p_details 
FROM mk_cde_lignes AS Lcde 
INNER JOIN mk_piece_article AS p 
ON Lcde.p_id = p.p_id 
LEFT OUTER JOIN mk_cde_pcomments AS c 
ON  c.cde_id = Lcde.cde_id
WHERE Lcde.cde_id = 4 
ORDER BY p_reference;

Cordialement

[SQLpro]

Postez la description de vos tables sous forme de DDL (CREATE TABLE ....) ceci nous permettra de connaitre les clef étrangères, de voir si votre modèle est correct et de faire les bonnes jointures !

A +

[hadeslabs]

Bonjour,
Voici ci-dessous, mes tables.

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
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
 
CREATE TABLE IF NOT EXISTS `mk_cdes` (
`cde_id` int(11) NOT NULL,
  `nom` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'cdeEnCours',
  `date` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `mk_cde_lignes`
--
 
CREATE TABLE IF NOT EXISTS `mk_cde_lignes` (
`cdeLigne_id` int(11) NOT NULL,
  `cde_id` int(11) NOT NULL,
  `p_id` int(11) NOT NULL,
  `qte` tinyint(4) unsigned zerofill NOT NULL,
  `livraison` char(10) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `mk_cde_pcomments`
--
 
CREATE TABLE IF NOT EXISTS `mk_cde_pcomments` (
`pcc_id` int(11) NOT NULL,
  `cde_id` int(11) NOT NULL,
  `p_id` int(11) NOT NULL,
  `cde_p_details` text COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `mk_piece_article`
--
 
CREATE TABLE IF NOT EXISTS `mk_piece_article` (
`p_id` int(11) unsigned zerofill NOT NULL,
  `p_reference` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `p_designation` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `p_machine` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `p_commentaire` text COLLATE utf8_unicode_ci,
  `p_famille` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `p_nb_actuel` smallint(11) DEFAULT NULL,
  `p_nb_used` smallint(11) DEFAULT NULL,
  `p_nb_mini` smallint(11) DEFAULT NULL,
  `p_nb_maxi` smallint(11) DEFAULT NULL,
  `p_emplacement` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `p_photo` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `prix` smallint(8) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=314 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
ALTER TABLE `mk_cdes`
 ADD PRIMARY KEY (`cde_id`), ADD UNIQUE KEY `nom` (`nom`);
 
--
-- Index pour la table `mk_cde_lignes`
--
ALTER TABLE `mk_cde_lignes`
 ADD PRIMARY KEY (`cdeLigne_id`);
 
--
-- Index pour la table `mk_cde_pcomments`
--
ALTER TABLE `mk_cde_pcomments`
 ADD PRIMARY KEY (`pcc_id`);
 
--
-- Index pour la table `mk_piece_article`
--
ALTER TABLE `mk_piece_article`
 ADD PRIMARY KEY (`p_id`), ADD UNIQUE KEY `p_reference` (`p_reference`);

Cordialement.

[escartefigue]

Bonjour,

Si votre DDL est complet alors il manque les contraintes de type REFERENCE
Ces contraintes sont fondamentales, car ce sont celles-ci qui permettent de faire le lien entre les tables et de garantir l'intégrité de votre base de données

En l'état, rien n'interdit d'avoir des identifiants orphelins dans les différentes tables où ces identifiants sont FK

Par contre pour cette remarque :
Avec cette requête, j'ai le commentaire respectif des articles, mais si un article de cette commande figure dans une autre commande, il est également affiché alors qu'il ne fait pas partie de la commande n°4 de l'exemple ci-dessous.
Le commentaire étant associé à un article, il est tout à fait normal qu'il apparaisse dans toutes les lignes de commandes qui concernent cet article
il est possible que faute de contrainte d'intégrité, vos identifiants FK soient faux, et que du coup vous récupériez des résultats innatendus

Pour le vérifier, faites des test exhaustifs d'existence d'orphelins entre vos différentes tables (select ... where not exists), puis nettoyez si besoin, et ajoutez les contraintes de type REFERENCE

[hadeslabs]

Bonjour,
Suite aux remarques de Monsieur escartefigue, en tout cas, c'est ainsi qu'on évoque son nom à Marseille,
j'ai dû récréer les 4 tables afin de pouvoir établir les contraintes ci-dessous. Donc, il est impossible d'insérer
une donnée dans les tables à contraintes sans qu'elles ne soient toutes validées. Merci encore.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
--
-- Contraintes pour la table `mk_cde_lignes`
--
ALTER TABLE `mk_cde_lignes`
ADD CONSTRAINT `articleObligatoire` FOREIGN KEY (`p_id`) REFERENCES `mk_piece_article` (`p_id`),
ADD CONSTRAINT `cde_Obligatoire` FOREIGN KEY (`cde_id`) REFERENCES `mk_cdes` (`cde_id`);
 
--
-- Contraintes pour la table `mk_cde_pcomments`
--
ALTER TABLE `mk_cde_pcomments`
ADD CONSTRAINT `pccid_articleObli` FOREIGN KEY (`p_id`) REFERENCES `mk_piece_article` (`p_id`),
ADD CONSTRAINT `ppcid_cdeObli` FOREIGN KEY (`cde_id`) REFERENCES `mk_cdes` (`cde_id`);

Par contre, j'ai beau tourné, je n'arrive pas à me représenter l'idée SQL suivante :
Si dans ma commande cde_id = 4, il y a des pièces qui ont un commentaire, il faudra l'afficher,
sachant qu'une pièce peut figurer dans une autre commande et avoir un autre commentaire.

Je vous remercie pour l'aide que vous m'apportez.

[escartefigue]

Par contre, j'ai beau tourné, je n'arrive pas à me représenter l'idée SQL suivante :
Si dans ma commande cde_id = 4, il y a des pièces qui ont un commentaire, il faudra l'afficher,
sachant qu'une pièce peut figurer dans une autre commande et avoir un autre commentaire.

Je vous remercie pour l'aide que vous m'apportez.

Votre table commentaires possède deux clefs étrangères (FK)
L'une, permet de retrouver l'article, il s'agit d'un commentaire lié à l'article, et donc ce quelque soit la commande
L'autre, permet de retrouver la commande, il s'agit d'un commentaire lié à la commande quelque soit l'article
Si vous souhaitez un commentaire lié à l'article ET à la commande, alors il faut une autre FK, qui pointe sur la ligne de commande

[escartefigue]

Si vous utilisez un outil de modélisation, les contraintes de type référence sont générées automatiquement


Voici un MCD simplifié :

Pièce jointe 232051

Notez que j'ai utilisé l'identification relative de la ligne de commande par rapport à la commande, cette id relative est matérialisée par les parenthèses autour des cardinalités
C'est à dire que l'identifiant de la commande fait partie intégrante de la PK de la ligne de commande
Cette solution est intéressante d'un point de vue performances, mais n'a pas d'incidence sur le reste


Avec ce MCD, on génère automatiquement le MLD suivant :

Pièce jointe 232052

Après génération du MPD dont je n'ai pas inclus le schéma ici, je peux visualiser le détail du script, et notamment la création des contraintes (script généré pour MySQL 5.0)
En voici un extrait pour la table des commentaires :

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
22
23
24
drop table if exists COMMENTAIRE_CMT;
 
/*==============================================================*/
/* Table : COMMENTAIRE_CMT                                      */
/*==============================================================*/
create table COMMENTAIRE_CMT
(
   CMT_ID               int not null auto_increment,
   ART_ID               int,
   LIG_CDE_ID           int,
   LCD_ID               int,
   CDE_ID               int,
   CMT_TEXTE            varchar(100) not null,
   primary key (CMT_ID)
);
 
alter table COMMENTAIRE_CMT add constraint FK_ASSOCIATION_3 foreign key (CDE_ID)
      references COMMANDE_CDE (CDE_ID);
 
alter table COMMENTAIRE_CMT add constraint FK_ASSOCIATION_4 foreign key (ART_ID)
      references ARTICLE_ART (ART_ID);
 
alter table COMMENTAIRE_CMT add constraint FK_ASSOCIATION_5 foreign key (LIG_CDE_ID, LCD_ID)
      references LIGNE_COMMANDE_LCD (CDE_ID, LCD_ID);

Vous voyez ici tout l'intérêt d'utiliser un logiciel de modélisation, le script est généré, il est quasi complet, et surtout, toutes les contraintes de type REFERENCE sont automatiquement créées, ce qui garantit l'intégrité de votre BD.
il faut toutefois penser à ajouter les options ON DELETE / ON UPDATE sur les contraintes
il faut également ajouter les CREATE INDEX de performance, seuls ceux liés aux PK et FK sont générés automatiquement

Si vous n'avez pas de logiciel de modélisation, vous pouvez en télécharger gratuitement, il en existe de très bons et faciles d'utilisation
à titre privé j'utilise DB-main qui est gratuit et complet, en entreprise, j'utilise power-AMC, complet aussi mais payant

[hadeslabs]

Bonjour,
Monsieur escartefigue, vous m'avez convaincu, je vais télécharger un soft de modélisation. Et désormais, je ne traverserai plus le Vieux Port à la nage, je prendrai le bateau de ce célèbre marin qui n'a jamais quitté le port. Vos explications sont très claires. J'admire également votre générosité en comptant le temps que vous avez consacré à mon problème. Grâce à vous, je vais beaucoup avancer car j'étais bien coincé.
Je vous remercie encore. Je clôture ce billet avec deux richesses : la solution à mon problème, et le désir d'aller apprendre à utiliser une application de modélisation de base de données.
Cordialement.

[escartefigue]

Excellente initiative et bienvenue au club