Pb: select count(distinct) left outer join avec clé primaire composé

Bonjour tml :)
alors voilà, je souhaiterai faire une requete sur une table de catégories, et afficher en meme temps un COUNT(DISTINCT) à partir d'une autre table nommée usercategs avec un LEFT OUTER JOIN sur le champs cat_nomint, Foreign key sur la seconde table (usercategs).
j'ai déja réaliser ce type de requete sur d'autres tables chose qui a fonctionné parfaitement jusqu'à mtn, sauf que là la différence est que ma table usercategs qui est composé de 3 colones (nom, prenom et cat_nomint), possède comme clé primaire, les trois champs en question cad nom, prenom et cat_nomint.
dans ce cas, ma requete qui devrai etre de la forme :
SELECT cat.*, COUNT(DISTINCT uc.nom, uc.prenom) AS nbr_uc FROM categories AS cat
LEFT OUTER JOIN usercategs AS uc ON cat.cat_nomint = uc.cat_nomint
GROUP BY cat.cat_nomint ...
ne fonctionne pas.
pour essayer d'etre un peu plus claire, je voudrai pouvoir lister les catégories et le nombre d'utilisateur par catégorie (sans redondance d'où le DISTINCT), en sachant qu'un utilisateur est reconnu par un clé composé des champs nom et prenom.
Pour précision, j'utilise postgresql 8.1 sur une linux debian etch 4.0.
Merci à l'avance de votre aide :)

Hello,

Désolé, j'ai un peu de mal à comprendre ce que tu as comme schéma ...

La prochaine fois, merci de respecter ceci : http://www.developpez.net/forums/showthread.php?t=944

Citation:

---

"3) Donnez les ordres SQL de création de vos tables (CREATE TABLE) ainsi que les INSERT d'un jeu de données basique afin que tout un chacun puisse reproduire ce que vous voulez faire sur son SGBDR afin de mieux vous aider."

---

En tous cas, de ce que j'ai compris, tu n'arrive pas à faire une jointure sur 3 colonnes? Si c'est bien le problème, modifie ta requete comme ceci :

Citation:

---

table1 LEFT JOIN table2 on (table1.col1 = table2.colA AND table1.col2 = table2.colB AND table1.col3 = table2.colC)

---

c'est à dire avec un AND entre chaque critère de jointure.

++

Salut,
dsl de pas avoir été claire.
j'ai donc essayer de simplifier le shmea pour ne garder que les partie qui consernent mon pb, donc voilà, y'a une petite capture d'ecran prise sous windows d'un modèle physique de poweramc, et le script correspondant:
/*==============================================================*/
create table USERCATEGS (
NOM TEXT not null,
PRENOM TEXT not null,
CAT_NOMINT TEXT not null,
constraint PK_USERCATEGS primary key (NOM, PRENOM, CAT_NOMINT)
);

/*==============================================================*/
/* Index : USERCATEGS_PK */
/*==============================================================*/
create unique index USERCATEGS_PK on USERCATEGS (
NOM,
PRENOM,
CAT_NOMINT
);

/*==============================================================*/
/* Table : UTILISATEURS */
/*==============================================================*/
create table UTILISATEURS (
NOM TEXT not null,
PRENOM TEXT not null,
USER_INFO TEXT not null,
constraint PK_UTILISATEURS primary key (NOM, PRENOM)
);

/*==============================================================*/
/* Index : UTILISATEURS_PK */
/*==============================================================*/
create unique index UTILISATEURS_PK on UTILISATEURS (
NOM,
PRENOM
);

alter table USERCATEGS
add constraint fk_categories foreign key (CAT_NOMINT)
references CATEGORIES (CAT_NOMINT)
on delete cascade on update cascade;

alter table USERCATEGS
add constraint fk_utilistaeurs foreign key (NOM, PRENOM)
references UTILISATEURS (NOM, PRENOM)
on delete cascade on update cascade;

/*==============================================================*/


et je rapel grossomodo, ce que je voudrai obtenir:
j'essai d'ecrire une requete qui m'affiche toutes le catégories et en meme temps, un count du nombre d'utilisateurs (nom, prenom) associés à ces catégories grace à la table USERCATEGS, en sachant qu'au niveau de la table UTILISATEUR, ma clé primaire est composé des deux champs nom et prenom, et que donc en créant une foreign key à partir de la table UTILISATEURS, ma foreign key est elle aussi composé de ces deux champs.

Dans d'autre cas, où ma foreign key n'est composé que d'une seul champs, ma requete aurai été:

SELECT cat.*, COUNT(DISTINCT uc.nom) AS nbr_uc FROM CATEGORIES AS cat
LEFT OUTER JOIN USERCATEGS AS uc ON cat.cat_nomint = uc.cat_nomint

mais comme ma clé est composé, j'essai de faire un COUNT(DISTINCT uc.nom, uc.prenom) mais sans résultat :( .

Salut,

Citation:

---

Envoyé par sinus

j'essai d'ecrire une requete qui m'affiche toutes le catégories et en meme temps, un count du nombre d'utilisateurs (nom, prenom) associés à ces catégories grace à la table USERCATEGS,

---

Code:

---

1 2 3 4

select cat.* ,count(lnk.*) from categories cat left outer join usercategs lnk on (lnk.cat_nomint = cat.cat_nomint ) group by cat.cat_info,cat.cat_nomint

---

Citation:

---

Envoyé par sinus

en sachant qu'au niveau de la table UTILISATEUR, ma clé primaire est composé des deux champs nom et prenom, et que donc en créant une foreign key à partir de la table UTILISATEURS, ma foreign key est elle aussi composé de ces deux champs.

---

As tu vraiment besoin d'utiliser la table utilisateur? D'après ce que tu explique au dessus, je ne comprend pas où?

++

Salut,

Bon j'ai l'impression que j'ai fais une petite bourde en copiant le script sql exemple, et que j'ai oublier de mettre la partie création de la table categories, donc le revoilà:

/*==============================================*/

create table CATEGORIES (
CAT_NOMINT TEXT not null,
CAT_INFO TEXT not null,
constraint PK_CATEGORIES primary key (CAT_NOMINT)
);

create unique index CATEGORIES_PK on CATEGORIES (
CAT_NOMINT
);

create table USERCATEGS (
NOM TEXT not null,
PRENOM TEXT not null,
CAT_NOMINT TEXT not null,
constraint PK_USERCATEGS primary key (NOM, PRENOM, CAT_NOMINT)
);

create unique index USERCATEGS_PK on USERCATEGS (
NOM,
PRENOM,
CAT_NOMINT
);

create table UTILISATEURS (
NOM TEXT not null,
PRENOM TEXT not null,
USER_INFO TEXT not null,
constraint PK_UTILISATEURS primary key (NOM, PRENOM)
);

create unique index UTILISATEURS_PK on UTILISATEURS (
NOM,
PRENOM

);

alter table USERCATEGS
add constraint fk_categories foreign key (CAT_NOMINT)
references CATEGORIES (CAT_NOMINT)
on delete cascade on update cascade;

alter table USERCATEGS
add constraint fk_utilistaeurs foreign key (NOM, PRENOM)
references UTILISATEURS (NOM, PRENOM)
on delete cascade on update cascade;
/*==============================================*/

Bon la capture d'ecran de poweramc reste valable. je sais que mon exemple est un peu dure à comprendre, parceque ce n'est qu'une partie d'une base de 70 tables qu'il serai fort ennueux de lister :s . Bref, on va supposer qu'il y a 6 enregistrement dans la table UTILISATEURS :

/*==============================================================*/

INSERT INTO utilisateurs (nom, prenom, user_info) VALUES ('dupont', 'jean', 'user1 infos');

INSERT INTO utilisateurs (nom, prenom, user_info) VALUES ('dupont', 'ludovic', 'user2 infos');

INSERT INTO utilisateurs (nom, prenom, user_info) VALUES ('montageni', 'eric', 'user3 infos');

INSERT INTO utilisateurs (nom, prenom, user_info) VALUES ('milano', 'eric', 'user4 infos');

INSERT INTO utilisateurs (nom, prenom, user_info) VALUES ('trevor', 'estelle', 'user5 infos');

INSERT INTO utilisateurs (nom, prenom, user_info) VALUES ('dupontenvis', 'marie', 'user6 infos');

/*==============================================================*/


on remarque qu'on a deux utilisateurs dont le nom est dupont, mais de prenom différent et deux utilisateurs qui portent le prenom eric, mais qui on des nom de famille différents, ma clé au niveau de la table utilisateurs etant constraint PK_UTILISATEURS primary key (NOM, PRENOM).


On va supposer aussi qu'il y a 4 enregistrements au niveau de la table CATEGORIES:

/*==============================================================*/

INSERT INTO categories (cat_nomint, cat_info) VALUES ('categ1' , 'categ 1 info ...');INSERT INTO categories (cat_nomint, cat_info) VALUES ('categ2' , 'categ 2 info ...');

INSERT INTO categories (cat_nomint, cat_info) VALUES ('categ3' , 'categ 3 info ...');

INSERT INTO categories (cat_nomint, cat_info) VALUES ('categ4' , 'categ 4 info ...');

/*==============================================================*/


Un utilisateur pouvant être dans une ou plusieurs catégories, et un catégorie pouvant posséder plusieurs utilisateurs, la relation entre la table UTILISATEURS et CATEGORIES n'est pas directe, mais se fait grâce à la table intermédiaire USERCATEGS (relations M:N).


Donc voici maintenant un exemple de ce que pourrai contenir la table USERCATEGS:

/*==============================================================*/

INSERT INTO usercategs (cat_nomint, nom, prenom) VALUES ('categ1', 'dupont', 'jean');

INSERT INTO usercategs (cat_nomint, nom, prenom) VALUES ('categ1', 'dupont', 'ludovic');

INSERT INTO usercategs (cat_nomint, nom, prenom) VALUES ('categ1', 'trevor', 'estelle');

INSERT INTO usercategs (cat_nomint, nom, prenom) VALUES ('categ2', 'montageni', 'eric');

INSERT INTO usercategs (cat_nomint, nom, prenom) VALUES ('categ2', 'milano', 'eric');

INSERT INTO usercategs (cat_nomint, nom, prenom) VALUES ('categ2', 'dupontenvis', 'marie');

INSERT INTO usercategs (cat_nomint, nom, prenom) VALUES ('categ3', 'trevor', 'estelle');

INSERT INTO usercategs (cat_nomint, nom, prenom) VALUES ('categ3', 'dupontenvis', 'marie');

/*==============================================================*/


Bon jusque là tout va bien, meme si on nottera qu'il y a 2 utilisateurs portant un meme nom et un prenom différent, et deux utilisateurs ayant le meme prenom et de nom de famille différents.

Le résultat que je souhaite obtenir devrait etre de la forme

____________________________________
cat_nomint | cat_info | nbr_uc
____________________________________

categ1 | 'categ 1 info ...' | 3
categ2 | 'categ 2 info ...' | 3
categ3 | 'categ 3 info ...' | 2
categ4 | 'categ 4 info ...' | 0
____________________________________


les requêtes que j'ai utilisé :

/*==============================================================*/

SELECT cat.*, COUNT(DISTINCT uc.nom)

FROM categories AS cat

LEFT OUTER JOIN usercategs AS uc ON cat.cat_nomint = uc.cat_nomint

GROUP BY cat.cat_nomint, cat.cat_info

ORDER BY cat.cat_nomint

/*==============================================================*/

m'affiche le résultat suivants :

cat_nomint | cat_info | nbr_uc

____________________________________
cat_nomint | cat_info | nbr_uc
____________________________________
categ1 | 'categ 1 info ...' | 2
categ2 | 'categ 2 info ...' | 3
categ3 | 'categ 3 info ...' | 2
categ4 | 'categ 4 info ...' | 0
____________________________________

/*==============================================================*/

SELECT cat.*, COUNT(DISTINCT uc.prenom)

FROM categories AS cat

LEFT OUTER JOIN usercategs AS uc ON cat.cat_nomint = uc.cat_nomint

GROUP BY cat.cat_nomint, cat.cat_info

ORDER BY cat.cat_nomint

/*==============================================================*/

m'affiche le résultat suivants :

____________________________________
cat_nomint | cat_info | nbr_uc
____________________________________
categ1 | 'categ 1 info ...' | 3
categ2 | 'categ 2 info ...' | 2
categ3 | 'categ 3 info ...' | 2
categ4 | 'categ 4 info ...' | 0
____________________________________

/*==============================================================*/

SELECT cat.*, COUNT(uc.*)

FROM categories AS cat

INNER JOIN usercategs AS uc ON cat.cat_nomint = uc.cat_nomint

GROUP BY cat.cat_nomint, cat.cat_info

ORDER BY cat.cat_nomint

/*==============================================================*/

m'affiche le résultat suivants :

____________________________________
cat_nomint | cat_info | nbr_uc
____________________________________
categ1 | 'categ 1 info ...' | 3
categ2 | 'categ 2 info ...' | 3
categ3 | 'categ 3 info ...' | 2
____________________________________


La première requête ne retourne pas un résultat correcte, elle me calcule qu'il y a 2 utilisateurs associés à la catégorie 1, alors qu'il y en a trois, mais c'est normal, c'est un COUNT(DISTINCT uc.nom), et j'ai deux utilisateurs de même nom et de prénom différents associés à la catégorie 1.

la deuxième requête ne retourne pas un résultat correcte, elle me calcule qu'il y a 2 utilisateurs associés à la catégorie 2, alors qu'il y en a trois, mais c'est normal, c'est un COUNT(DISTINCT uc.prenom), et j'ai deux utilisateurs de même prénom et de nom différents associés à la catégorie 2.

la troisième requête afficher un résultat exact au niveau du nombre d'utilisateurs par catégories, mais ne retourne pas les catégories qui n'ont aucun utilisateurs associé (grâce à la table USERCATEGS).

je dois donc essayer de trouver un mélange entre la requêté 1 et la requête 2, c-a-d un truc du style :

/*==============================================================*/

SELECT cat.*, COUNT(DISTINCT uc.nom, uc.prenom)
FROM categories AS cat
LEFT OUTER JOIN usercategs AS uc ON cat.cat_nomint = uc.cat_nomint
GROUP BY cat.cat_nomint, cat.cat_info
ORDER BY cat.cat_nomint

/*==============================================================*/

mais le problème se situe au niveau du COUNT(DISTINCT uc.nom, uc.prenom)
:( :( :(

Et ma requete? t'as essayé?;)

select cat.* ,count(lnk.*)
from categories cat
left outer join usercategs uc on (cat.cat_nomint = uc.cat_nomint )
group by cat.cat_info,cat.cat_nomint

Ta requete, oui j'ai essayer, ça marche dans le cadre où tu n'as qu'un seul COUNT à faire au niveau de la requete. Marheureusement dans mon cas, je me retrouve avec 2 COUNT à faire d'où l'obligation d'utiliser DISTINCT, parceque sinon je me retrouve avec un produit :s.

aprofondissements du problème :
on n'as plus 3 tables, mais 5. les trois première cité en haut, plus uns table nomé LANGUAGUES destinée à contenit les information sur les langues des pays, et possédant un clé unique sur un seul champs, et une table intermédiaire entre la table CATEGORIES et la table LANGUAGES, nomé CATI18N, destiné à contenir les traductions de chaque catégories dans les langues de la table LANGUAGES.


en supposant que ma table LANGUAGES comprenne 3 enregistrements, et que la catégorie categ1 est associé avec 2 langues, la requete suivante:

SELECT cat.* , COUNT(uc.*) AS nbr_uc, COUNT(DISTINCT ci18n.lng_id) AS nbr_lang
FROM categories cat
LEFT OUTER JOIN usercategs uc on (uc.cat_nomint = cat.cat_nomint )
LEFT OUTER JOIN cati18n ci18n on (ci18n.cat_nomint = cat.cat_nomint )
group by cat.cat_info,cat.cat_nomint

devrai m'afficher le résultat suivant:
_____________________________________________
cat_nomint | cat_info | nbr_uc | nbr_lang
_____________________________________________

categ1 | 'categ 1 info ...' | 6 | 2
categ2 | 'categ 2 info ...' | 0 | 0
categ3 | 'categ 3 info ...' | 0 | 0
categ4 | 'categ 4 info ...' | 0 | 0
_____________________________________________

qui correspond au produit du nombre de tradution avec le nombre d'association categ-user, d'où le nbr_uc = 6 = 2*3 pour categ1, 0 = 3*0, 0= 2*0, 0 = 0*0 etc ...

le mot clé DISTINCT me permet d'eviter ce genre de problème lors de COUNT avec jointure multiples.

mon problème se situe au niveau de l'utilisation de DISTINCT au niveau de deux champs en meme temps (nom, prenom) et non au niveau d'un seul, parceque ces deux champs composent la clé primaire de la table UTILISATEURS:

constraint PK_UTILISATEURS primary key (NOM, PRENOM)

et

alter table USERCATEGS
add constraint fk_utilistaeurs foreign key (NOM, PRENOM)
references UTILISATEURS (NOM, PRENOM)
on delete cascade on update cascade;