Comparer deux listes dans deux tables

**djongar** · 18/01/2012, 11h54

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 :

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

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 :

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

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;

**lola06** · 18/01/2012, 12h09

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 ?

**CinePhil** · 18/01/2012, 12h10

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

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.

**djongar** · 18/01/2012, 13h50

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

Envoyé par 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.

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

Envoyé par lola06

Et nous mettre ton ébauche de requête ?

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 :

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

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'}};

**CinePhil** · 18/01/2012, 14h25

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...

Code :

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

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 !

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 (*) !

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 :

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

1
2
3
SELECT COUNT(*) AS nb_phases
FROM phase
WHERE Phase_reference = 1

Combien de phases validées pour le lot 1 ?

Code :

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

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 :

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

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 :

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

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 :

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

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
)

**djongar** · 18/01/2012, 14h56

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

Envoyé par CinePhil

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

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

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 (*) !

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

Envoyé par CinePhil

Combien de phases pour le lot 1 ?

Code :

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

1
2
3
SELECT COUNT(*) AS nb_phases
FROM phase
WHERE Phase_reference = 1

Combien de phases validées pour le lot 1 ?

Code :

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

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.

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.

Envoyé par CinePhil

Combien de phases par lot ?

Code :

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

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 :

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

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 :

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

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

**lola06** · 18/01/2012, 15h16

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.

**CinePhil** · 18/01/2012, 15h36

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.

Comparer deux listes dans deux tables

Langage SQL

Discussions similaires

Partager

Partager