Count dans plusieurs tables

1 pièce(s) jointe(s)

Bonjour à tous et toutes!

J'ai une base de données (SQL MANAGER) avec plusieurs tables "principale" (contacts, société, etc...) dans laquelle chaque entrée peut-être reliée à x autres, du coup des tables de relations existent entre chacune d'entre elle (contact-contact, contact-société, contacts-..., ...).

Je voudrais une requête qui me permet de voir à combien d'autre enregistrement de chaque type est relié chaque contact. Voilà la requête que j'ai faite :

Dès que plusieurs Count à une valeur supérieur à 0 un produit cartésien est fait entre ceux-ci...
Par exemple ci-dessous, la 2ème ligne est correcte, mais pas la 1ère: les 0 sont corrects mais normalement: Actions=6, Publications=13 et Profiles=5 (6X13X5=390)
Pièce jointe 357152
Quelqu'un peut-il m'aider svp ? Plus tard j'exporterais ces données dans un fichier Excel

Merci à d'avance!^^

PS: la structure de la base de données n'est pas modifiable, elle vient d'un crm.

J'ai également essayé avec cette requête, j'ai exactement le même résultat

Bonjour,

Deux solutions :

1/ vous pouvez ajouter un DISTINCT dans les COUNT de votre deuxième requête (il faut bien faire un distinct sur les clef des tables jointes)
Code:

1 2 3 4 ... ,Count(DISTINCT ContDocu.K_DOCUMENT) AS Documents ...
Soit vous préagregez dans des sous requetes :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT ... , DOCU_CONT.Nb AS Documents ... FROM ... LEFT JOIN ( SELECT K_CONTACT , COUNT(*) AS Nb FROM DOCU_CONT GROUP BY K_CONTACT ) ContDocu ON ContDocu.K_CONTACT=Cont.K_CONTACT ...

1 pièce(s) jointe(s)

Merci pour votre réponse!

La 1ere requete (avec les DISTINCT) est vraiment longue à l'execution (25min et aucune ligne n'est sortie sur les 2350 attendues)

En revanche je suis presque arrivé à faire fonctionner la 2ème : Je n'arrive pas à faire la bonne jointure entre CONTACTS et COMPANIES à cause de la condition sur COMPANIES.KIND
Le résultat de la query correspond a peu près à l'image ci-dessous. Dans mon cas A est ma table CONTACTS, B ma table COMPANIES et l'intersection est ma table CONT_COMP la requête doit sortir:
- chaque ligne de A (chaque CONTACTS) qui ne sont pas dans l'intersection (donc CONT_COMP.K_COMPANY IS NULL), donc la colonne ContComp.Nb=0 ou null
ET
- chaque ligne de A (chaque CONTACTS) qui sont présents dans l'intersection où COMPANIES.KIND=12 : ces lignes doivent avoir la colonne ContComp.Nb égale ou nombre de lien
Pièce jointe 357491

J'ai 1 requête qui fonctionne presque:

Pour la 1ère : le chiffre dans les colonnes sont bons, problème c'est qu'elles me sort TOUS les CONTACTS. En gros faut filtrer les COMPANIES.KIND!=12.
J'ai essayé de mettre cette condition entre la ligne 19 et 20 (dans un WHERE) ça ne marche pas,
J'ai aussi essayé dans le ON ligne 19, ça ne marche pas non plus

Citation:

INNER JOIN COMPANIES Comp ON (CONT_COMP.K_COMPANY=Comp.K_COMPANY AND Comp.KIND=12)

Et enfin j'ai esayé de faire comme dans mon exemple, ça ne marche pas non plus..
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 29 30 31 32 /* SURPLUS Comp.KIND!=12 */ SELECT Cont.NAME AS Nom ,Cont.FIRSTNAME AS Prenom ,ContComp.Nb AS Companies ,ContCont.Nb AS Contacts ,ContDocu.Nb AS Documents ,ContActi.Nb AS Actions ,ContPubl.Nb AS Publications ,ContOppo.Nb AS Opportunities ,ContMail.Nb AS Mails ,ContProd.Nb AS Products ,ContProj.Nb AS Projects ,ContProf.Nb AS Profiles ,ContTmpl.Nb AS Templates FROM CONTACTS Cont LEFT JOIN ( SELECT K_CONTACT, Comp.KIND, COUNT(*) AS Nb FROM CONT_COMP INNER JOIN COMPANIES Comp ON CONT_COMP.K_COMPANY=Comp.K_COMPANY GROUP BY K_CONTACT, Comp.KIND ) ContComp ON (ContComp.K_CONTACT=Cont.K_CONTACT AND KIND=12) LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM CONT_CONT GROUP BY K_CONTACT) ContCont ON ContCont.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM DOCU_CONT GROUP BY K_CONTACT) ContDocu ON ContDocu.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM ACTI_CONT GROUP BY K_CONTACT) ContActi ON ContActi.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM PUBL_CONT GROUP BY K_CONTACT) ContPubl ON ContPubl.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM OPPO_CONT GROUP BY K_CONTACT) ContOppo ON ContOppo.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM MAIL_CONT GROUP BY K_CONTACT) ContMail ON ContMail.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM PROD_CONT GROUP BY K_CONTACT) ContProd ON ContProd.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM PROJ_CONT GROUP BY K_CONTACT) ContProj ON ContProj.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM PROF_CONT GROUP BY K_CONTACT) ContProf ON ContProf.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM TMPL_CONT GROUP BY K_CONTACT) ContTmpl ON ContTmpl.K_CONTACT=Cont.K_CONTACT ORDER BY ContComp.Nb, Cont.NAME, Cont.FIRSTNAME

quel genre de doublons ? vous ne voulez les compter qu'une fois ? vous pouvez aussi mettre un COUNT(DISTINCT ...) dans les sous requêtes.

Bonjour,

Désolé pour le temps de réponse.

J'ai modifié la requête et je n'ai plus le problème de doublons, en revanche la requête me sort tous les contacts de la base de données. Je n'arrive pas à placer le "WHERE Comp.KIND=12".

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
29
30
31
SELECT Cont.NAME AS Nom
      ,Cont.FIRSTNAME AS Prenom
      ,ContComp.Nb AS Companies
      ,ContCont.Nb AS Contacts
      ,ContDocu.Nb AS Documents
      ,ContActi.Nb AS Actions
      ,ContPubl.Nb AS Publications
      ,ContOppo.Nb AS Opportunities
      ,ContMail.Nb AS Mails
      ,ContProd.Nb AS Products
      ,ContProj.Nb AS Projects
      ,ContProf.Nb AS Profiles
      ,ContTmpl.Nb AS Templates
        FROM CONTACTS Cont
        LEFT JOIN (
          SELECT K_CONTACT, KIND, COUNT(*) AS Nb
          FROM CONT_COMP
          INNER JOIN COMPANIES Comp ON CONT_COMP.K_COMPANY=Comp.K_COMPANY
            GROUP BY K_CONTACT, Comp.KIND
        ) ContComp ON (ContComp.K_CONTACT=Cont.K_CONTACT AND KIND=12)
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM CONT_CONT GROUP BY K_CONTACT) ContCont ON ContCont.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM DOCU_CONT GROUP BY K_CONTACT) ContDocu ON ContDocu.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM ACTI_CONT GROUP BY K_CONTACT) ContActi ON ContActi.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM PUBL_CONT GROUP BY K_CONTACT) ContPubl ON ContPubl.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM OPPO_CONT GROUP BY K_CONTACT) ContOppo ON ContOppo.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM MAIL_CONT GROUP BY K_CONTACT) ContMail ON ContMail.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM PROD_CONT GROUP BY K_CONTACT) ContProd ON ContProd.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM PROJ_CONT GROUP BY K_CONTACT) ContProj ON ContProj.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM PROF_CONT GROUP BY K_CONTACT) ContProf ON ContProf.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM TMPL_CONT GROUP BY K_CONTACT) ContTmpl ON ContTmpl.K_CONTACT=Cont.K_CONTACT
          ORDER BY Cont.NAME, Cont.FIRSTNAME

J'ai essayé de placer le WHERE KIND=12 tout en bas (juste au dessus du ORDER BY mais ce n'est pas bon non plus).

quelle est la multiplicité contact-->companies ?

que faire si un contact est rattaché a une compagnie avec KIND = 12, et une autre avec KIND = 14 ?

En clair, que doit vous remonter la requête après filtrage sur KIND = 12 ?

1 pièce(s) jointe(s)

Bonjour,

Vous pouvez voir contacts et les autres tables autours de celle-ci comme une "étoile", avec une table de relation dans chaque lien
CONTACTS -- CONT_COMP -- COMPANIES
CONTACTS -- CONT_DOCU -- DOCUMENTS
CONTACTS -- CONT_OPPO -- OPPORTUNITIES
CONTACTS -- CONT_CONT -- CONTACTS (lien sur elle même
CONTACTS -- ...

Voici un exemple que j'ai fait avec Excel, il y a les différentes tables avec leurs relation, ainsi que le tableau de résultats attendus :
Pièce jointe 362311

Sur l'Excel chaque contacts à une couleur.
Les 3 première colonnes du tableau "QUERY RESULTS" représente les informations du contact : sa clé (K_CONTACT), son nom et son prénom.
Chacune des autres représente le nombre de lien qu'il possède avec chaque table:
- Opportunities = nombre de liens entre le contacts et la table OPPORTUNITIES
- Documents = nombre de liens entre le contacts et la table DOCUMENTS
- Companies = nombre de liens entre le contacts et la table COMPANIES

/!\ : le nombre de liens avec des sociétés (Companies) ne compte que les liens avec les sociétés où KIND=12 (cf sociétés en rouge dans la table COMPANIES)
/!\ : les contacts liés seulement à des sociétés qui n'a pas KIND=12 ne doivent pas apparaître (comme le contact 2)
/!\ : les contacts qui ne sont liés à rien (contact 5) et ceux qui ne sont lier à aucune sociétés (ex contact 4) doivent apparaître aussi

J'ai fait ça, mais je n'arrive pas a placer le "WHERE Comp.KIND=12" au bon endroit (en gros c'est comme ci dans mon exemple ci-dessus le contact 2 était affiché)
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 29 30 31 32 SELECT Cont.NAME AS Nom ,Cont.FIRSTNAME AS Prenom ,ContComp.Nb AS Companies ,ContCont.Nb AS Contacts ,ContDocu.Nb AS Documents ,ContActi.Nb AS Actions ,ContPubl.Nb AS Publications ,ContOppo.Nb AS Opportunities ,ContMail.Nb AS Mails ,ContProd.Nb AS Products ,ContProj.Nb AS Projects ,ContProf.Nb AS Profiles ,ContTmpl.Nb AS Templates FROM Actilan.adminactilan.CONTACTS Cont LEFT JOIN ( SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.CONT_COMP INNER JOIN Actilan.adminactilan.COMPANIES Comp ON CONT_COMP.K_COMPANY=Comp.K_COMPANY WHERE Comp.KIND=12 GROUP BY K_CONTACT ) ContComp ON ContComp.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.CONT_CONT GROUP BY K_CONTACT) ContCont ON ContCont.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.DOCU_CONT GROUP BY K_CONTACT) ContDocu ON ContDocu.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.ACTI_CONT GROUP BY K_CONTACT) ContActi ON ContActi.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.PUBL_CONT GROUP BY K_CONTACT) ContPubl ON ContPubl.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.OPPO_CONT GROUP BY K_CONTACT) ContOppo ON ContOppo.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.MAIL_CONT GROUP BY K_CONTACT) ContMail ON ContMail.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.PROD_CONT GROUP BY K_CONTACT) ContProd ON ContProd.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.PROJ_CONT GROUP BY K_CONTACT) ContProj ON ContProj.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.PROF_CONT GROUP BY K_CONTACT) ContProf ON ContProf.K_CONTACT=Cont.K_CONTACT LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.TMPL_CONT GROUP BY K_CONTACT) ContTmpl ON ContTmpl.K_CONTACT=Cont.K_CONTACT ORDER BY Cont.NAME, Cont.FIRSTNAME

Si j'ai bien compris, il suffit dans cette dernière requête de faire une jointure interne (INNER JOIN) sur la sous première sous-critique (celle qui récupère les infos des sociétés et où vous avez placé votre filtre KIND=12)

Ainsi, si un contact n'a aucune société avec KIND=12, alors il sera supprimé du résultat du fait de la jointure interne.

remplacez en ligne 15 le LEFT JOIN par un INNER JOIN

J'avais essayé, mais si je fait ça il me manque tous les contacts qui ne sont pas liés à une société, c'est à dire ceux qui n'apparaissent pas dans CONT_COMP (en gros dans l'exemple que j'ai mis les contacts 4 et 5 n'apparaissent pas), pour ça que j'avais mis un LEFT JOIN

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
24
25
26
27
28
29
30
31
32
33
 
SELECT Cont.NAME AS Nom
      ,Cont.FIRSTNAME AS Prenom
      ,ContComp.Nb AS Companies
      ,ContCont.Nb AS Contacts
      ,ContDocu.Nb AS Documents
      ,ContActi.Nb AS Actions
      ,ContPubl.Nb AS Publications
      ,ContOppo.Nb AS Opportunities
      ,ContMail.Nb AS Mails
      ,ContProd.Nb AS Products
      ,ContProj.Nb AS Projects
      ,ContProf.Nb AS Profiles
      ,ContTmpl.Nb AS Templates
        FROM Actilan.adminactilan.CONTACTS Cont
        LEFT JOIN (
          SELECT K_CONTACT, COUNT(*) AS Nb MAX(CASE WHEN Comp.KIND=12 THEN 1 ELSE 0 END) AS Kind12
          FROM Actilan.adminactilan.CONT_COMP
          INNER JOIN Actilan.adminactilan.COMPANIES Comp ON CONT_COMP.K_COMPANY=Comp.
            GROUP BY K_CONTACT
        ) ContComp ON ContComp.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.CONT_CONT GROUP BY K_CONTACT) ContCont ON ContCont.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.DOCU_CONT GROUP BY K_CONTACT) ContDocu ON ContDocu.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.ACTI_CONT GROUP BY K_CONTACT) ContActi ON ContActi.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.PUBL_CONT GROUP BY K_CONTACT) ContPubl ON ContPubl.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.OPPO_CONT GROUP BY K_CONTACT) ContOppo ON ContOppo.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.MAIL_CONT GROUP BY K_CONTACT) ContMail ON ContMail.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.PROD_CONT GROUP BY K_CONTACT) ContProd ON ContProd.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.PROJ_CONT GROUP BY K_CONTACT) ContProj ON ContProj.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.PROF_CONT GROUP BY K_CONTACT) ContProf ON ContProf.K_CONTACT=Cont.K_CONTACT
        LEFT JOIN (SELECT K_CONTACT, COUNT(*) AS Nb FROM Actilan.adminactilan.TMPL_CONT GROUP BY K_CONTACT) ContTmpl ON ContTmpl.K_CONTACT=Cont.K_CONTACT
	   WHERE Kind12 = 1 OR Kind12 IS NULL
          ORDER BY Cont.NAME, Cont.FIRSTNAME

Ca marche nickel merci, je passe le sujet en résolu.