Comparer deux listes dans deux tables

Version imprimable

Voici le problème que j'ai:
Un lot est composé de plusieurs phases, l'état du lot sera changé en "receptionner" quand toutes les phases liées à cet lot sont validées. Donc si on résume il nous faut une requête qui vérifie l'état de toutes les phases affectées à un lot sont validées.
la forme du table lot est la suivante:

Code:

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `lot` (
  `Appel_offre_reference` int(11) NOT NULL,
  `Lot_reference` int(11) NOT NULL,
  `Marche_reference` int(11) default NULL,
  `Lot_libelle` varchar(254) default NULL,
  `Lot_etat` varchar(254) default 'non receptionner',
  PRIMARY KEY  (`Appel_offre_reference`,`Lot_reference`),
  KEY `FK_lot_marche` (`Marche_reference`),
  CONSTRAINT `FK_appelOffre_lot` FOREIGN KEY (`Appel_offre_reference`) REFERENCES `appeloffre` (`Appel_offre_reference`),
  CONSTRAINT `FK_lot_marche` FOREIGN KEY (`Marche_reference`) REFERENCES `marche` (`Marche_reference`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

et la forme de la Table Phase:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `phase` (
  `Appel_offre_reference` int(11) NOT NULL,
  `Lot_reference` int(11) NOT NULL,
  `Phase_reference` int(11) NOT NULL,
  `Reglement_type_operation_financiere` varchar(254) default NULL,
  `Phase_libelle` varchar(254) default NULL,
  `Phase_etat` varchar(254) default 'non valider',
  PRIMARY KEY  (`Appel_offre_reference`,`Lot_reference`,`Phase_reference`),
  KEY `FK_phase_reglement` (`Reglement_type_operation_financiere`),
  CONSTRAINT `FK_lot_phase` FOREIGN KEY (`Appel_offre_reference`, `Lot_reference`) REFERENCES `lot` (`Appel_offre_reference`, `Lot_reference`),
  CONSTRAINT `FK_phase_reglement` FOREIGN KEY (`Reglement_type_operation_financiere`) REFERENCES `reglement` (`Reglement_type_operation_financiere`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

18/01/2012, 12h09
lola06

Bonjour,

Peut-tu nous préciser ton SGBD ?
Et nous mettre ton ébauche de requête ?

Par contre je n'ai pas trop compris ta problèmatique :
- tu veux afficher seulement les phases dont tous les lots sont "receptionner" ?
- tu veux voir les états de tous les lots de toutes tes phases ?
- autre chose ?
18/01/2012, 12h10
CinePhil

Tu as essayé une requête avant de nous demander de faire ton travail ? :?

Citation:

Un lot est composé de plusieurs phases, l'état du lot sera changé en "receptionner" quand toutes les phases liées à cet lot sont validées. Donc si on résume il nous faut une requête qui vérifie l'état de toutes les phases affectées à un lot sont validées.

Il faut donc comparer le nombre de phases d'un lot et le nombre de phases validées de ce même lot.

Citation:

Envoyé par lola06

Bonjour,

Peut-tu nous préciser ton SGBD ?

Par contre je n'ai pas trop compris ta problèmatique :
- tu veux afficher seulement les phases dont tous les lots sont "receptionner" ?
- tu veux voir les états de tous les lots de toutes tes phases ?
- autre chose ?

J'utilise SQL comme SGBD.
je veux vérifier si la liste des phases qui appartient à un lot sont toutes validées pour qu'on puisse dire après un lot est réceptionné avec toutes ses phases validées

Citation:

Envoyé par CinePhil

Tu as essayé une requête avant de nous demander de faire ton travail ?

Citation:

Citation:
Un lot est composé de plusieurs phases, l'état du lot sera changé en "receptionner" quand toutes les phases liées à cet lot sont validées. Donc si on résume il nous faut une requête qui vérifie l'état de toutes les phases affectées à un lot sont validées.

Il faut donc comparer le nombre de phases d'un lot et le nombre de phases validées de ce même lot.

Oui c'est exactement ça pour savoir si les phases de ce lot sont toutes validées ou pas.

Citation:

Citation:

Envoyé par lola06

Et nous mettre ton ébauche de requête ?

Citation:

Envoyé par CinePhil

Tu as essayé une requête avant de nous demander de faire ton travail ?

Oui j'ai essayé de faire un essai en raisonnant de chercher les phases validées qui appartiennent à un lot donné et de la comparer à la liste des phases d'un lot donné.
Code:

1 2 3 SELECT * FROM LOT lot WHERE lot.id IN {SELECT * FROM LOT lot LEFT JOIN PHASE phase WHERE phase.Phase_reference=lot.id AND phase.Phase_etat ALL IN {SELECT * FROM PHASE ph WHERE ph.Phase_etat='valider'}};

Citation:

J'utilise SQL comme un SGBD.

SQL est un langage, objet de ce forum.

On te demande quel est ton SGBD (système de gestion de bases de données). Par exemple, Postgresql, Microsoft SQL Server, Oracle, IBM DB2, MySQL...
Citation:
Code:

1 2 3 SELECT * FROM LOT lot WHERE lot.id IN {SELECT * FROM LOT lot LEFT JOIN PHASE phase WHERE phase.Phase_reference=lot.id AND phase.Phase_etat ALL IN {SELECT * FROM PHASE ph WHERE ph.Phase_etat='valider'}};
Euh...
Toi tu as besoin de cours en SQL ! :aie:

Jamais vu d'accolades dans une requête SQL ! Peut-être est-ce une syntaxe particulière de ton SGBD ?

WHERE lot.id IN {SELECT * ne peut pas fonctionner car tu compares la valeur d'une colonne (lot.id) à un ensemble de colonnes (*) ! :aie:

Il y aurait encore pas mal de choses à dire sur la requête mais j'ai autre chose à faire...

Combien de phases pour le lot 1 ?
Code:

1 2 3 SELECT COUNT(*) AS nb_phases FROM phase WHERE Phase_reference = 1
Combien de phases validées pour le lot 1 ?
Code:

1 2 3 4 SELECT COUNT(*) AS nb_phases_validees FROM phase WHERE Phase_reference = 1 AND Phase_etat = 'valider'
Bonjour la faute au passage ! "valider" est un verbe, pas un adjectif. Et il serait préférable de ne pas répéter ce terme dans de nombreuses lignes de la table mais de faire référence à une table des états.

Combien de phases par lot ?
Code:

1 2 3 4 SELECT Phase_reference AS id_lot, COUNT(*) AS nb_phase FROM phase GROUP BY Phase_reference
Combien de phases validées par lot ?
Code:

1 2 3 4 5 SELECT Phase_reference AS id_lot, COUNT(*) AS nb_phase FROM phase WHERE Phase_etat = 'valider' GROUP BY Phase_reference
Quels sont les lots qui ont toutes leurs phases validées ?
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT p1.Phase_reference AS id_lot, COUNT(p1.*) AS nb_phase FROM phase p1 WHERE p1.Phase_etat = 'valider' GROUP BY p1.Phase_reference HAVING COUNT(p1.*) = ( SELECT COUNT(p2.*) FROM phase p2 WHERE p2.Phase_reference = p1.Phase_reference )

Citation:

Envoyé par CinePhil

SQL est un langage, objet de ce forum.

On te demande quel est ton SGBD (système de gestion de bases de données). Par exemple, Postgresql, Microsoft SQL Server, Oracle, IBM DB2, MySQL..

Désolé je veux bien dire MySQL :oops:

Citation:

Envoyé par CinePhil

Euh...
Toi tu as besoin de cours en SQL ! :aie:

Je confirme que j'ai oublié des notions de base en SQL, surtout dans ma requête je n'ai pas pu montrer la comparaison d'une liste à une autre enfin c'est ma question dans ce post :mrgreen:

Citation:

Envoyé par CinePhil

Jamais vu d'accolades dans une requête SQL ! Peut-être est-ce une syntaxe particulière de ton SGBD ?

WHERE lot.id IN {SELECT * ne peut pas fonctionner car tu compares la valeur d'une colonne (lot.id) à un ensemble de colonnes (*) ! :aie:

Il y aurait encore pas mal de choses à dire sur la requête mais j'ai autre chose à faire...

Tu as raison pour les accolades mais je les utilisais au lieu des parenthèses pour mieux expliquer le code mais apparemment j'ai fait le contraire :aie:
Citation:
Envoyé par CinePhil
Combien de phases pour le lot 1 ?
Code:

1 2 3 SELECT COUNT(*) AS nb_phases FROM phase WHERE Phase_reference = 1
Combien de phases validées pour le lot 1 ?
Code:

1 2 3 4 SELECT COUNT(*) AS nb_phases_validees FROM phase WHERE Phase_reference = 1 AND Phase_etat = 'valider'
Citation:

Bonjour la faute au passage ! "valider" est un verbe, pas un adjectif. Et il serait préférable de ne pas répéter ce terme dans de nombreuses lignes de la table mais de faire référence à une table des états.
J'ai utilisé un verbe et pas un adjectif parce que à la base de donnée j'ai eu des problèmes en mettant "validé" et pas la peine d'une Table des états vu que c'est juste un champ pour vérifier l'état du lot ou de la phase concernée.
Citation:
Envoyé par CinePhil
Combien de phases par lot ?
Code:

1 2 3 4 SELECT Phase_reference AS id_lot, COUNT(*) AS nb_phase FROM phase GROUP BY Phase_reference
Combien de phases validées par lot ?
Code:

1 2 3 4 5 SELECT Phase_reference AS id_lot, COUNT(*) AS nb_phase FROM phase WHERE Phase_etat = 'valider' GROUP BY Phase_reference
Quels sont les lots qui ont toutes leurs phases validées ?
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT p1.Phase_reference AS id_lot, COUNT(p1.*) AS nb_phase FROM phase p1 WHERE p1.Phase_etat = 'valider' GROUP BY p1.Phase_reference HAVING COUNT(p1.*) = ( SELECT COUNT(p2.*) FROM phase p2 WHERE p2.Phase_reference = p1.Phase_reference )
Merci bien pour les idées, je vous informe pour le résultat :ccool:

18/01/2012, 15h16
lola06

Citation:

Envoyé par djongar

J'ai utiliser un verbe et pas un adjectif parce que à la base de donnée j'ai eu des problèmes en mettant "validé" et pas la peine d'une Table des états vu que c'est juste un champ pour vérifier l'état du lot ou de la phase concernée.

Dans ce cas pourquoi ne pas utilisé le nom, parce que CinePhil a raison ça fait vraiment mal aux yeux !!

- valider / ? non valider ? -----> valide / invalide
- non receptionner / receptionner -----> reception KO / reception OK

Pourquoi tu ne veux pas construire une table des états ? D'autant plus que si tu l'utilise dans une requête un peu lourde, le fait de requêter sur un ID est bien moins gourmand que sur un texte.
18/01/2012, 15h36
CinePhil

L'externalisation des données de référence fait partie des bonnes pratiques de modélisation des données.

La table des états est jusitifée s'il y a plusieurs états (commande reçue, chantier programmé, en cours, terminé, réceptionné, réserves levées, validé).

Au passage, je ne vois pas pourquoi une colonne de type VARCHAR refuserait des lettres accentuées ! C'est peut-être plutôt un problème de compatibilité CHARSET entre la table SQL et la page HTML.

Si par contre, la colonne ne sert qu'à dire si une phase est validée ou non, un type BOOLEAN serait suffisant.