Besoin d'une fonction pour trouver des éléments identiques dans une table

Soit une table contenant un champs nolot, un champ nom et un champs prenom. J'ai besoin, en entrant un nolot en argument de la fonction, quels sont tous les lots ayant toutes les mêmes valeurs pour nom et prénom.

Je pense qu'un exemple serait utile.

Soit la table

Citation:

---

nolot | prénom | nom
---------------------
4000 | Albert | Einstein
4000 | Mileva | Einstein
4001 | Marie | Curie
4001 | Pierre | Curie
4002 | Albert | Einstein
4003 | Barack | Obama
4004 | Albert | Einstein
4004 | Mileva |Einstein

---

J'ai besoin de sortir, pour formule(4000) la liste suivante

Citation:

---

nolot
-----
4000
4004

---

4004 doit sortir parce qu'il contient Albert Einstein ET Mileva Einstein, tout comme 4000 qui est en argument de la formule.
4002 ne devra pas sortir parce qu'il ne contient pas Mileva Einstein.

Je n'ai pas encore fais assez de formules pour PostgreSQL pour réussir à faire celle là, mais j'en ai besoin.
Je sêche, aussi je fais appel à vous.

Merci de vos z'avis z'avisés,
Jean-Marc

Il s'agit d'un cas de division relationnelle assez classique. Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/divrelationnelle/

A +

bonjour et merci de ce tuto exemplaire.

Je sêche toutefois encore. Je suis dans le dernier cas : le cas de la division exacte.
Je n'arrive pas à ramener tout cela à une seule table source.
Dans votre exemple d'appareils, admettons qu'il faille sortir tous les appareils contenant les mêmes composants, ni plus, ni moins, et que, de plus, un composant pourrait se retrouver plusieurs fois.
Pour se ramener à mon cas, il se peut qu'un lot ait 2 Albert Einstein (car il y a des lots ou des personnes différentes portent le même nom et prénom) et une Milesa Einstein. Il faudrait donc trouver tous les lots contenant 2 Albert Einstein et 1 Milesa Einstein, tout en sachant aussi que le nom et le prénom sont dans des champs séparés. Je ne sais pas si, pour simplifier, il serait bon de concaténer le nom et le prénom ou si cela, dans certains cas, pourrait nous induire en erreur.
En fait, il me faut trouver tous les lots ayant toutes les mêmes personnes que le lot dont le numéro est entré en argument dans la fonction.

Merci toutefois de l'attention que vous portez à ma recherche,

JM

Il y a une solution relativement simple en passant par des tableaux intermédiaires. Simple mais peu performante, à voir en fonction des volumes si c'est un problème ou pas.

L'idée est de placer dans un tableau dynamique tous les couples nom-prénom correspondant à chaque lot particulier, par une requête du style:

Code:

---

SELECT nolot, array_agg(row(prenom, nom)) AS liste FROM lot GROUP BY nolot

---

A combiner avec l'opérateur d'égalité des tableaux pour trouver n'importe quel lot qui a exactement le même contenu qu'un autre lot, par exemple pour le 4000:

Code:

---

1 2 3

select nolot from (select nolot, array_agg(row(prenom, nom)) AS liste from lot group by nolot) s1 where liste=(select array_agg(row(prenom, nom)) from lot where nolot=4000);

---

La fonction array_agg est dispo en standard à partir de PG 8.4 seulement, mais si version antérieure il suffit de la créer, voir l'exemple de array_accum dans la documentation.

Visiblement mes articles ne vous ont pas inspiré. Pour autant la solution y figure bien :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE JeanMarc68 (nolot int, prenom varchar(8), nom varchar(8));   INSERT INTO JeanMarc68 VALUES (4000, 'Albert', 'Einstein'); INSERT INTO JeanMarc68 VALUES (4000, 'Mileva', 'Einstein'); INSERT INTO JeanMarc68 VALUES (4001, 'Marie', 'Curie'); INSERT INTO JeanMarc68 VALUES (4001, 'Pierre', 'Curie'); INSERT INTO JeanMarc68 VALUES (4002, 'Albert', 'Einstein'); INSERT INTO JeanMarc68 VALUES (4003, 'Barack', 'Obama'); INSERT INTO JeanMarc68 VALUES (4004, 'Albert', 'Einstein'); INSERT INTO JeanMarc68 VALUES (4004, 'Mileva', 'Einstein ');

---

-- solution

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

WITH T AS (SELECT nom, prenom FROM JeanMarc68 INTERSECT SELECT nom, prenom FROM JeanMarc68 WHERE nolot = 4000) SELECT nolot FROM JeanMarc68 AS JM INNER JOIN T ON JM.nom = T.nom AND JM.prenom = T.prenom GROUP BY nolot HAVING COUNT(*) = (SELECT COUNT(*) FROM T)

---

Code:

---

1 2 3 4 5

nolot ----------- 4000 4004

---

Et si vous voulez apprendre le SQL, mon site, comme mes bouquins, peuvent vous y aider !

A +

La requête que tu proposes ne convient pas à cause de la contrainte qui est mentionnée sur les doublons de prénom et nom:

Citation:

---

Pour se ramener à mon cas, il se peut qu'un lot ait 2 Albert Einstein (car il y a des lots ou des personnes différentes portent le même nom et prénom) et une Milesa Einstein. Il faudrait donc trouver tous les lots contenant 2 Albert Einstein et 1 Milesa

---

1) si vous ne nous donnez pas dès la départ tous les éléments c'est stupide de votre part. Or je ne voit nullement mentionné ce que vous dite et le jeu de données comme le résultat correspond bien. J'ai donc la désagréable l'impression d'avoir été pris pour un con et d'avoir travaillé pour vous en vain !
2) si votre table admet des doublons purs, votre requête est mathématiquement insolvable en l'état.

A +

Bonjour et merci à tous les 2 de vous être penchés sur mon cas.

Pour SQLPro, c'était estofilo qui vous a répondu en dernier et je pense sincèrement que personne ne vous prend pour un con ici. Pour une référence certainement, pour un con jamais.
Je suis en effet un autodidacte dans le domaine et je ne connaissais pas votre site que j'ai pû découvrir grâce à votre première réponse. J'y vais régulièrement m'instruire depuis ces quelques jours.

Pour la string SQL, je pense en effet que vous avez raison et que la solution était décrite. Je n'ai juste pas été capable de la monter moi-même. C'est l'intersect de la table sur elle même qui ne m'est venu à l'esprit, je pense.

Enfin, pour le doublon, je reconnais l'avoir oublié dans le premier post.
Ce fait arrive rarement mais en effet il est déjà arrivé qu'un lot ait plusieurs propriétaires ayant exactement le même nom et le même prénom.
Toutefois je peux le déterminer a posteriori il me semble.
En effet, admettons que le lot 4000 contienne 1 Mileva Einstein et 2 Albert Einstein. Le lots 4004 sortira mais le 4000 ne sortira plus à cause de la vérification des count (*) si je ne me trompe pas. En effet les 2 lots contiennent les mêmes noms, mais pas le même nombre de noms.
Dès lors, si le lot recherché ne sort pas dans le résultat, on peut déterminer qu'aucun autre lot le lui correspond, à moins que ma logique ne soit pas bonne, ce qui est possible.

Encore une fois merci à tous les 2 et croyez moi sincèrement, je ne prend PERSONNE pour un con.

Jean-Marc

OK pour la méprise !!!! ;-)

Voici la rectification à apporter pour que cela marche avec des doublons :

Code:

---

1 2	HAVING COUNT(DISTINCT JM.nom + '#' + JM.prenom) = (SELECT COUNT(*) FROM T)

---

A +

Je dois être bien bouché mais je n'y arrive pas.

J'ai créé une nouvelle table avec un id et tous les noms en minuscule (PostgreSQL n'aime pas trop les majuscules, mais cela n'a aucune importance et il n'aime pas les tables sans clé primaire. L'id sert donc juste à avoir une clé primaire dans la table).
J'ai ajouté Un Albert Einstein dans le lot 4000 dont j'ai fais une copie pour avoir le lot 4005. Le lot 4006 est une copie du 4004. Le lot 4007 ne contient que Mileva Einstein et le lot 4008 contient le couple Einstein et Marie Curie (Pour avoir 3 personnes, comme dans 4000 et 4005 mais des personnes différentes)

Code:

---

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

CREATE TABLE jm68 ( id bigint NOT NULL, nolot integer, prenom character varying(8), nom character varying(8), CONSTRAINT jm68_pkey PRIMARY KEY (id) )   INSERT INTO jm68 VALUES (0,4000, 'Albert', 'Einstein'); INSERT INTO jm68 VALUES (1,4000, 'Albert', 'Einstein'); INSERT INTO jm68 VALUES (2,4000, 'Mileva', 'Einstein'); INSERT INTO jm68 VALUES (3,4001, 'Marie', 'Curie'); INSERT INTO jm68 VALUES (4,4001, 'Pierre', 'Curie'); INSERT INTO jm68 VALUES (5,4002, 'Albert', 'Einstein'); INSERT INTO jm68 VALUES (6,4003, 'Barack', 'Obama'); INSERT INTO jm68 VALUES (7,4004, 'Albert', 'Einstein'); INSERT INTO jm68 VALUES (8,4004, 'Mileva', 'Einstein '); INSERT INTO jm68 VALUES (9,4005, 'Albert', 'Einstein'); INSERT INTO jm68 VALUES (10,4005, 'Albert', 'Einstein'); INSERT INTO jm68 VALUES (11,4005, 'Mileva', 'Einstein'); INSERT INTO jm68 VALUES (12,4006, 'Albert', 'Einstein'); INSERT INTO jm68 VALUES (13,4006, 'Mileva', 'Einstein'); INSERT INTO jm68 VALUES (14,4007, 'Mileva', 'Einstein'); INSERT INTO jm68 VALUES (15,4008, 'Albert', 'Einstein'); INSERT INTO jm68 VALUES (16,4008, 'Mileva', 'Einstein'); INSERT INTO jm68 VALUES (17,4008, 'Marie', 'Curie');

---

Je devrais donc avoir 4000 et 4005 si je cherche les lots ayant les mêmes propriétaires que 4000 et avoir 4004 et 4006 si je cherche les lots ayant les mêmes propriétaires que 4004.

J'ai PostrgreSQL 8.3 et il sembe le pas aimer les With.
J'ai donc fais ceci sur base des tes posts précédents

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

SELECT nolot FROM jm68 JM INNER JOIN (SELECT nom, prenom FROM jm68 INTERSECT SELECT nom, prenom FROM jm68 WHERE nolot = 4000) T ON JM.nom = T.nom AND JM.prenom = T.prenom GROUP BY nolot HAVING COUNT(DISTINCT JM.nom || '#' || JM.prenom) = (SELECT COUNT(*) FROM (SELECT nom, prenom FROM jm68 INTERSECT SELECT nom, prenom FROM jm68 WHERE nolot = 4000) T )

---

Cette string sql me sort, pour le lot 4000 : 4000,4004,4005,4006, 4008

Je me dis que c'est dû au Distinct dans le count puisque celui-ci regroupe toutes les personnes de même prénom et même nom. Je pense qu'il y aurait donc lieu de retirer le distinc des 2 bords du = dans le having, afin de ne plus regrouper ces personnes.
Je pense aussi que malgré la suppression de ces Distinct, un nombre égal de personnes différentes (Style Albert Einstein, Marie Curie et Barack Obama, ce qui fait 3 personnes aussi) ne devrait pas sortir puisqu'on est dans un having d'une table qui ne contient plus que les personnes du lot d'origine (suis-je clair ??? Je ne suis pas sur ...)
Cette réflexion m'amène ici :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT nolot FROM jm68 AS JM INNER JOIN (SELECT nom, prenom FROM jm68 INTERSECT SELECT nom, prenom FROM jm68 WHERE nolot = 4000) T ON JM.nom = T.nom AND JM.prenom = T.prenom GROUP BY nolot HAVING COUNT(JM.nom || '#' || JM.prenom) = (SELECT COUNT(*) FROM (SELECT nom, prenom FROM jm68 WHERE nolot = 4000) T )

---

Ici la requête sur le lot 4000 me sort bien 4000,4005.

Par contre la requête sur 4004 me sort 4004,4008,4006.
La requête sur le lot 4002 me retourne 4002,4004,4008,4006.

Aussi, je ne comprend pas en quoi l'intersect d'une table sur elle-même change qqch. Qqch m'échappe parce que pour moi

Code:

---

1 2 3 4 5 6 7

SELECT nom, prenom FROM jm68 INTERSECT SELECT nom, prenom FROM jm68 WHERE nolot = 4000

---

revient au même que

Code:

---

1 2 3 4

SELECT nom, prenom FROM jm68 WHERE nolot = 4000

---

Enfin, pour être clair, je ne dis pas que c'est la même chose, mais je dis que personnellement je ne vois pas la différence. Aussi j'aimerais qu'on m'éclaire sur la chose.

Je suis donc reparti à 0, j'ai relu votre tuto sur la division et j'ai adapté comme ceci :

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 nolot FROM jm68 Lots WHERE NOT EXISTS (SELECT * FROM (SELECT nom, prenom FROM jm68 WHERE nolot = 4000) PropriosLot WHERE NOT EXISTS (SELECT * FROM jm68 Proprios2 WHERE (Lots.nolot = Proprios2.nolot) AND (Proprios2.nom = PropriosLot.nom AND Proprios2.prenom = PropriosLot.prenom) ) ) GROUP BY nolot HAVING COUNT (*) = (SELECT COUNT(*) FROM (SELECT nom, prenom FROM jm68 WHERE nolot = 4000) PropriosLot )

---

Ici tout fonctionne sauf pour les 4000 et 4005 qui me sortent 4000,4005,4008.
Par contre le 4008 me sort bien uniquement 4008.

Le problème doit sans doute venir du doublon de Albert Einstein dans le 4000 et 4005. Il trouve bien Albert Einstein et Mileva Einstein dans les 3 lots et le nombre de propriétaires est exact (3 dans tous les cas).

J'ai l'impression d'être proche mais je ne trouve pas et ça me fâche.

Merci de votre attention et de l'aide que vous m'avez déjà apportée,
JM

A tout hasard je rappelle que cette requête est une solution exacte à ton problème depuis le début:

Code:

---

1 2 3

SELECT nolot FROM (SELECT nolot, array_agg(row(prenom, nom)) AS liste FROM lot GROUP BY nolot) s1 WHERE liste=(SELECT array_agg(row(prenom, nom)) FROM lot WHERE nolot=4000);

---

Pourquoi chercher midi à quatorze heure?
Les requêtes toutes faites de division relationnelle ne s'appliquent pas à ton jeu de données car l'existence de doublons dans le dividende change complètement la donne.

A propos de la sous-requête:

Code:

---

1 2 3 4 5 6

SELECT nom, prenom FROM jm68 INTERSECT SELECT nom, prenom FROM jm68 WHERE nolot = 4000

---

et de la question rattachée:

Citation:

---

Aussi, je ne comprend pas en quoi l'intersect d'une table sur elle-même change qqch

---

Effectivement avec ces ensembles là de part et d'autre cet opérateur d'intersection n'apporte rien. En tout cas

Code:

---

SELECT DISTINCT nom, prenom FROM jm68 WHERE nolot = 4000

---

produirait le même résultat. (à noter quand même le DISTINCT dû au fait qu'INTERSECT élimine les doublons).

Salut Estofilo,

Je suis sous 8.3 et j'avais créé la fonction array_agg comme tu me l'avais conseillé mais j'obtenais toujours une erreur (ERROR: could not find array type for data type record
État SQL :42704).
Vu mon niveau je n'arrivais pas à la supprimer et je pensais que c'était sans doute dû au fait que je devais faire qqch de plus dans la 8.3.
Pour finir j'ai fini par me rendre compte que le pbl était dû au row(prenom,nom).
J'ai donc remplacé le row(prenom, nom) par nom || '|' || prenom et j'ai ajouté un where de plus afin de n'obtenir que les autres lots ayant les mêmes personnes sans que le lot à partir duquel on cherche ne sorte.

J'ai donc écris ceci qui fonctionne :ccool::

Code:

---

1 2 3 4

SELECT nolot FROM (SELECT nolot, array_agg(nom || '|' || prenom) AS liste FROM jm68 GROUP BY nolot) s1 WHERE liste=(SELECT array_agg(nom || '|' || prenom) FROM jm68 WHERE nolot=4000) AND nolot<>4000;

---

Merci à toi Estofilo, et à SQLPro aussi pour le temps que vous avez passé à m'aider, et par la même occasion à m'instruire,

JM