count plusieurs tables

**supernicoco** · 15/12/2009, 17h12

Bonjour à tous.

J'ai un souci de resultat de requete sur des count et je pense que ma requete devrait de plus etre simplifiable...

J'ai 3 tables renseignées par des utilisateurs, et j'enregistre la date de saisie et l'utilisateur à chaque nouvelle enregistrement dans ces tables.
elles sont liés par l'id de la premiere .De plus, dans la premiere table, j'ai une adresse avec un code postal.
Enfin j'ai une table RegionDPT avec les numéro des départements,les noms et la régions de ces départements.

Je souhaite afficher par départements choisis et depuis une date saisie le nombres d'enregistrements de chacune de mes 3 tables pour un utilisateur donné.
Voici le stables et leurs champs utilisés.

FournisseurBien : IdFournisseurBien,Datesaisie,Utilisateur,CP,Ville...
ContactFournisseurBien:IdContact,IdFournisseurBien,Datesaisie,Utilisateur...
suiviFournisseurBien:IdFournisseur,Datecontact,Utilisateur...

Les champs CP et NumDPT sont des varchar et je suis sous sqlserver2008.

Voici ma requete actuel:

Code :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT NumDPT,NomDPT,NomRegion
,COUNT(case f.Utilisateur when 'User1' then 1  end ) AS mesfour
,COUNT(case c.Utilisateur when 'User1' then 1  end  )AS mescont 
,COUNT(case h.Utilisateur when 'User1' then 1  end )AS messuiv
FROM RegionDPT
JOIN FournisseurBien f ON RegionDPT.NumDPT=substring(f.CP,1,2) 
full OUTER JOIN ContactFournisseurBien c ON 
f.IdFournisseurBien=c.IdFournisseurBien
full OUTER JOIN suiviFournisseurBien h ON f.IdFournisseurBien=h.IdFournisseur 
WHERE NumDPT IN (01,03,07,10,14,18) 
AND (f.Datesaisie >'01/11/2009' OR f.Datesaisie IS NULL) 
AND (c.Datesaisie >'01/11/2009'  OR c.Datesaisie IS NULL) 
AND (h.Datecontact>'01/11/2009'  OR h.Datecontact IS NULL)
GROUP BY NumDPT,NomDPT,NomRegion

Mes résultats sont "presque" bons.J'ai parfois une ligne où le resultat ne correspond pas au nombre d'enregistrements...
Merci de m'orienter vers une solution plus ...rigoureuse

... fausse manip, j'ai posté 2 fois dont une fois pas en entier...

**Dokho1000** · 15/12/2009, 17h33

Ce serait plus simple si tu mettais la requete que tu as tenté de faire...?

**rsc** · 16/12/2009, 09h02

Dans la ligne :

Code :

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

,COUNT(case f.Utilisateur when 'User1' then 1  end ) AS mesfour

il me semble que tu devrais utiliser SUM, puisque le principe, c'est que tu additionnnes les 1, et complèter par une valeur 0 dans le cas contraire, pour éviter les problèmes de NULL :

Code :

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

,SUM(case f.Utilisateur when 'User1' then 1  else 0 end ) AS mesfour

**al1_24** · 16/12/2009, 09h24

En ramenant les restrictions au niveau des jointures, la requête sera légèrement optimisée.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT    NumDPT    
    ,    NomDPT
    ,    NomRegion
    ,    SUM(CASE f.Utilisateur WHEN 'User1' THEN 1  END) AS mesfour
    ,    SUM(CASE c.Utilisateur WHEN 'User1' THEN 1  END) AS mescont
    ,    SUM(CASE h.Utilisateur WHEN 'User1' THEN 1  END) AS messuiv
FROM    RegionDPT
    JOIN    FournisseurBien f 
        ON    RegionDPT.NumDPT = SUBSTRING(f.CP,1,2)
        AND f.Datesaisie > '01/11/2009'
    FULL OUTER JOIN ContactFournisseurBien c 
        ON    f.IdFournisseurBien = c.IdFournisseurBien
        AND c.Datesaisie > '01/11/2009'
    FULL OUTER JOIN suiviFournisseurBien h 
        ON    f.IdFournisseurBien = h.IdFournisseur
        AND h.Datecontact > '01/11/2009'
WHERE    NumDPT IN (01, 03, 07, 10, 14, 18)
GROUP BY NumDPT
    ,    NomDPT
    ,    NomRegion

**Snipah** · 16/12/2009, 10h44

Bonjour,

Si pour vos "fournisseur" vous avez plus d'une correspondance dans "contact" ou "suivi", vos lignes seront dédoublées et les count() faux. Ca le sera d'autant plus s'il doit faire un produit cartésien entre "contact" et "suivi".
Selon vos données, il est préférable de faire count() ou sum() avant la jointure, groupé par la ou les colonnes servant dans les conditions de jointure.

**supernicoco** · 17/12/2009, 10h47

Merci a vous pour vos réponse.

J'ai modifié la requete comme conseillé avec des sum et mis les conditions dans les jointures.
Mais snipah a raison...J'ai bien plusieurs contacts par fournisseur et plusieurs historisations aussi...
je vais donc bien analyser sa phrase :

Selon vos données, il est préférable de faire count() ou sum() avant la jointure, groupé par la ou les colonnes servant dans les conditions de jointure.

Et modifier ma requete en conséquance.

**supernicoco** · 17/12/2009, 16h03

Heu... Aprés une matinée passé dessus, je cale tj...

J'ai des resultats comptés plusieurs fois ou dans plusieurs colonnes...
Je pense que ça vient des conditions de jointure que je n'arrive pas à exprimer correctement.

Je repose le probléme pour qu'il soit bien claire:
je veux recuperer le nb de fournisseur, contact et historisation créer par un user et par departement a partir d'une date donnée.
Je peux avoir pour mon user et pour un departement 0 à n fournisseur créé,0 à n contact et 0 à n historisation.
mes jointures ce font sur le code postal dans ma table fournisseuBien et sur le numero du departement et les 2 autres depuis l'idfournisseur (unique) de ma table FournisseurBien vers les idfournisseur (non unique) de mes tables ContactFournisseur et SuiviFournisseur.

Avec ma requete actuel les résultats ne sont pas bon si j'ai un fournisseur en commun avec mes contact ou mes histo...

voici ma requete actuelle:

Code :

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

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
34
 
SELECT    NumDPT    
    ,    NomDPT
    ,    NomRegion
    ,    sum(
     CASE  
     WHEN f.Utilisateur='user1'and  f.Datesaisie > '01/11/2009' 
     and SUBSTRING(f.CP,1,2)=RegionDPT.NumDPT
      THEN 1  ELSE 0 END ) AS mesfour
    ,    sum( 
    CASE  
    WHEN c.Utilisateur='user1'and  c.Datesaisie > '01/11/2009'
    and SUBSTRING(f.CP,1,2)=RegionDPT.NumDPT
         THEN 1  ELSE 0 END ) AS mescont
   ,    sum( 
    CASE  
    WHEN h.Utilisateur='user1'  AND h.Datecontact > '01/11/2009'
    and SUBSTRING(f.CP,1,2)=RegionDPT.NumDPT
    THEN 1 ELSE 0  END ) AS messuiv
 
FROM    RegionDPT
     JOIN    FournisseurBien f 
        ON    RegionDPT.NumDPT = SUBSTRING(f.CP,1,2)
 
    FULL OUTER JOIN ContactFournisseurBien c 
        ON    f.IdFournisseurBien = c.IdFournisseurBien
 
    FULL OUTER JOIN  suiviFournisseurBien h 
        ON    f.IdFournisseurBien= h.IdFournisseur
 
WHERE    NumDPT IN (44)
GROUP BY NumDPT
    ,    NomDPT
    ,    NomRegion

si je groupe sur mes conditions de jointure, ça me donne trop de lignes de résultat ou alors je groupe pas ou il faut ou ce qu'il faut...

**Waldar** · 17/12/2009, 16h08

Pouvez-vous fournir un petit jeu d'essai ?

**rsc** · 17/12/2009, 16h24

Et si tu fais comme ça, ça te donne quoi ? On ramène toutes les lignes correspondant aux conditions, puis on fait des COUNT DISTINCT sur les données que tu cherches.
(NB : j'ai essayé de deviner les noms des champs qui me manquaient, donc à corriger)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT    NumDPT    
    ,    NomDPT
    ,    NomRegion
    ,    COUNT (DISTINCT f.IdFournisseurBien) AS mesfour
    ,    COUNT (DISTINCT c.IdContact) AS mescont
   ,    COUNT (DISTINCT h.IdSuivi) AS messuiv
FROM    RegionDPT
JOIN    FournisseurBien f 
  ON RegionDPT.NumDPT = SUBSTRING(f.CP,1,2)
LEFT OUTER JOIN ContactFournisseurBien c 
  ON f.IdFournisseurBien = c.IdFournisseurBien
LEFT OUTER JOIN  suiviFournisseurBien h 
  ON f.IdFournisseurBien= h.IdFournisseur
WHERE NumDPT IN (44)
AND f.Utilisateur='user1'
AND  f.Datesaisie > '01/11/2009' 
AND SUBSTRING(f.CP,1,2)=RegionDPT.NumDPT
GROUP BY NumDPT
    ,    NomDPT
    ,    NomRegion

**supernicoco** · 18/12/2009, 11h43

Merci de vos reponses.
MAlheureusement rsc, la requete ne marche pas plus car je n'ai pas d'identifiant unique dans ma table suivifournisseur.

je mets un jeu d'essai.

RegionDPT
NumDPT NomDPT NomRegion
44 loire atlantique blabla
07 blabla blabla

FournisseurBien
IdFournisseurbien CP DateSaisie Utilisateur
1 44200 2009-11-30 user1
2 44300 2009-11-30 user1
3 44500 2009-11-30 user2

ContactFournisseurBien
IdFournisseurBien Utilisateur DateSaisie IdContact
1 user1 2009-11-30 1
1 user2 2009-11-30 2
2 user2 2009-11-30 3
3 user1 2009-11-30 4
3 user1 2009-11-30 5

SuiviFournisseurBien
IdFournisseur Datecontact Utilisateur commentaire
1 2009-11-30 user1 BLA
2 2009-11-30 user1 BLA
2 2009-11-30 user1 BLA

Ma requete me sort:
NumDPT NomDPT NomRegion mesfour mescont messuiv
44 loire atlantique blabla 4 3 4

au lieu de :
44 loire atlantique blabla 2 3 3

J'arrive a le faire en 3 requetes séparer donc je pense que le probléme vient des conditions de jointure mais je ne vois pas comment faire ces satanées jointures pour que ça marche...

**Snipah** · 18/12/2009, 11h45

Essayez ceci :

Code :

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

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
SELECT 
       rd.NumDPT,
       rd.NomDPT,
       rd.NomRegion,
       SUM(CASE f.Utilisateur WHEN 'User1' THEN 1  END) AS mesfour,
       SUM(c.mescont) AS mescont,
       SUM(s.messuiv) AS messuiv
FROM RegionDPT rd
JOIN FournisseurBien f
  ON RegionDPT.NumDPT = SUBSTRING(f.CP,1,2)	
LEFT JOIN 
     ( SELECT IdFournisseurBien, SUM(CASE Utilisateur WHEN 'User1' THEN 1  END) AS mescont
       FROM ContactFournisseurBien
       GROUP BY IdFournisseurBien
     ) c
     ON f.IdFournisseurBien = c.IdFournisseurBien
LEFT JOIN 
     ( SELECT IdFournisseurBien, SUM(CASE Utilisateur WHEN 'User1' THEN 1  END) AS messuiv
       FROM SuiviFournisseurBien
       GROUP BY IdFournisseurBien
     ) s
     ON f.IdFournisseurBien= s.IdFournisseur
WHERE rd.NumDPT IN (44)
AND f.Utilisateur='user1'
AND f.Datesaisie > '01/11/2009' 
GROUP BY 
      rd.NumDPT,
      rd.NomDPT,
      rd.NomRegion

**supernicoco** · 18/12/2009, 12h05

Merci de votre reponse Snipah.

j'ai modifier qq alias de table dans la requete pour qu'elle marche , ça donne:

Code :

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

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
 
SELECT 
       rd.NumDPT,
       rd.NomDPT,
       rd.NomRegion,
       SUM(CASE f.Utilisateur WHEN 'User1' THEN 1  END) AS mesfour,
       SUM(c.mescont) AS mescont,
       SUM(s.messuiv) AS messuiv
FROM RegionDPT rd
JOIN FournisseurBien f
  ON RegionDPT.NumDPT = SUBSTRING(f.CP,1,2)	
LEFT JOIN 
     ( SELECT IdFournisseurBien, SUM(CASE c.Utilisateur WHEN 'User1' THEN 1  END) AS mescont
       FROM ContactFournisseurBien
       GROUP BY IdFournisseurBien
     ) c
     ON f.IdFournisseurBien = c.IdFournisseurBien
LEFT JOIN 
     ( SELECT IdFournisseurBien, SUM(CASE h.Utilisateur WHEN 'User1' THEN 1  END) AS messuiv
       FROM SuiviFournisseurBien
       GROUP BY IdFournisseurBien
     ) s
     ON f.IdFournisseurBien= s.IdFournisseur
WHERE rd.NumDPT IN (44)
AND f.Utilisateur='user1'
AND f.Datesaisie > '01/11/2009' 
GROUP BY 
      rd.NumDPT,
      rd.NomDPT,
      rd.NomRegion

mais le resultat n'est pas encore atteind:
44 loire atlantique blabla 2 1 3
au lieuu de
44 loire atlantique blabla 2 3 3

...Mais ça m'a ouvert d'autre possibilité de recherche...

**Waldar** · 18/12/2009, 12h35

Question subsidaire, quel est le SGBD ?

Essayez ceci :

Code :

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

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
SELECT 
    rd.NumDPT,
    rd.NomDPT,
    rd.NomRegion,
    coalesce(sum(nb_f), 0) as mesfour,
    coalesce(sum(nb_c), 0) as mescont,
    coalesce(sum(nb_s), 0) as messuiv
FROM
    RegionDPT rd
    LEFT OUTER JOIN FournisseurBien FB
      ON rd.NumDPT = SUBSTRING(FB.CP, 1, 2)
    LEFT OUTER JOIN
    (
      select IdFournisseurBien, count(*) as nb_f
        from FournisseurBien 
       where Utilisateur = 'user1'
         and Datesaisie >= '01/11/2009'
    group by IdFournisseurBien   
    ) Four
      ON Four.IdFournisseurBien = FB.IdFournisseurBien
    LEFT OUTER JOIN 
    (
      select IdFournisseurBien, count(*) as nb_c
        from ContactFournisseurBien
       where Utilisateur = 'user1'
         and Datesaisie >= '01/11/2009'
    group by IdFournisseurBien
    ) Cont
      ON Cont.IdFournisseurBien = FB.IdFournisseurBien
    LEFT OUTER JOIN
    (
      select IdFournisseur, count(*) as nb_s
        from SuiviFournisseurBien
       where Utilisateur = 'user1'
         and Datecontact >= '01/11/2009'
    group by IdFournisseur   
    ) Suiv
      ON Suiv.IdFournisseur = FB.IdFournisseurBien
GROUP BY
    rd.NumDPT,
    rd.NomDPT,
    rd.NomRegion;
 
 
NumDPT NomDPT           NomRegion mesfour     mescont     messuiv
------ ---------------- --------- ----------- ----------- -----------
07     blabla           blabla    0           0           0
44     loire atlantique blabla    2           3           3

**supernicoco** · 18/12/2009, 15h40

C'est bon j'ai trouvé ...

voici la requete finale (qui peut surement etre améliorée mais qui renvoie les bons résultats):

Code :

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

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
34
35
36
 
SELECT 
       rd.NumDPT,
       rd.NomDPT,
       rd.NomRegion,
       sum(f.mesfour) AS mesfour,
       sum(c.mescont) AS mescont,
       SUM(s.messuiv) AS messuiv
FROM RegionDPT rd
JOIN 
FournisseurBien FF
  ON rd.NumDPT = SUBSTRING(FF.CP,1,2)	
  left join (select IdFournisseurBien,SUM(CASE  WHEN Utilisateur='user1'and DateSaisie>'01/11/2009' THEN 1 else 0 END) AS mesfour
  from FournisseurBien
 
  group by IdFournisseurbien) f
  on FF.IdFournisseurbien=f.IdFournisseurbien
left  JOIN 
     ( SELECT  IdFournisseurBien,SUM(CASE  WHEN Utilisateur='user1'and DateSaisie>'01/11/2009'THEN 1 else 0 END) AS mescont
       FROM ContactFournisseurBien 
 
       GROUP BY IdFournisseurBien
      ) c
     ON FF.IdFournisseurBien = c.IdFournisseurBien
LEFT JOIN 
     ( SELECT IdFournisseur, SUM(CASE  WHEN Utilisateur='user' and Datecontact>'01/11/2009' THEN 1 else 0 END) AS messuiv
       FROM SuiviFournisseurBien 
       GROUP BY IdFournisseur
     ) s
     ON FF.IdFournisseurBien= s.IdFournisseur
WHERE rd.NumDPT IN (44,21,35,75)
 
GROUP BY 
      rd.NumDPT,
      rd.NomDPT,
      rd.NomRegion

ce qui correspond a au dernier post de waldar d'ailleurs

il fallait joindre 2 fois FournisseurBien une fois pour faire la jointure avec les autres tables et une fois pour recuperer mes enregistrement désiré.

Pour la question subsidiaire, je suis sous sql server 2008

Un grand merci à tous.

count plusieurs tables

Langage SQL

Discussions similaires

Partager

Partager