Discussion :

[Thomas321]

Bonjour

J'ai un petit problème avec la sélection d'un nombre d'enregistrements répondant à un critère particulier.
J'ai 3 tables : CLIENTS --> DOSSIERS --> ETAT_DOSSIERS
- un client a de 0 à n dossiers
- un dossier a de 1 à 4 états
- chaque état de dossier est représenté par un enregistrement

Ex :

CLIENT | ID
--------------
| TOTO
| TITI

DOSSIER | ID | CLIENT_ID
---------------------------
| DOS_1 | TOTO
| DOS_2 | TOTO

ETAT_DOSSIER | ID | ETAT
---------------------------
| DOS_1 | ouvert
| DOS_1 | refusé
| DOS_2 | ouvert

J'aimerais donc récupérer :
TOTO 1 (on ne compte pas le dossier refusé)
TITI 0 (on compte les clients qui n'ont pas de dossiers)

Ma première tentative ne me ramène pas les clients qui n'ont pas de dossiers (normal à cause du count) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT CLIENTS.ID, COUNT(DISTINCT ETAT_DOSSIERS.ETAT)
FROM CLIENTS, DOSSIERS, ETAT_DOSSIERS
WHERE DOSSIERS.ID_CLIENT=CLIENTS.ID      
AND ETAT_DOSSIERS.ID = DOSSIERS.ID                            
AND ETAT_DOSSIERS.ETAT = 'ouvert'
AND ETAT_DOSSIERS.ID NOT IN ( 
    SELECT ETAT_DOSSIERS.ID
    FROM ETAT_DOSSIERS
    WHERE ETAT_DOSSIERS.ETAT IN ('fermé','abandonné','refusé')
) 
GROUP BY CLIENTS.ID

TOTO 1

- ok : ne compte pas DOS_1 qui est refusé
- ko : n'affiche pas TITI 0

Ma seconde tentative me permet de récupérer les clients sans dossiers mais je n'arrive pas à rajouter le critère de sélection sur l'état du dossier :

Code :

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

1
2
3
4
5
6
7
8
SELECT CLIENTS.ID, COUNT(DISTINCT ETAT_DOSSIERS.ETAT)
FROM CLIENTS                          
LEFT OUTER JOIN DOSSIERS ON DOSSIERS.CLIENT_ID=CLIENTS.ID    
     LEFT OUTER JOIN ETAT_DOSSIERS ON (                   
       ETAT_DOSSIERS.ID = DOSSIERS.ID
       AND ETAT_DOSSIERS.ETAT = 'ouvert'                     
     )                                                        
GROUP BY CLIENTS.ID

TOTO 2
TITI 0

- ko : compte DOS_1 qui est refusé
- ok : affiche bien TITI qui n'a pas de dossiers

Je n'arrive pas à rajouter les critères (select) dans le JOIN.
Si je rajoute mes critères avant le GROUP BY alors la requête ne ramène plus les clients sans dossiers.

Quelqu'un verrait il une solution ?
(je ne peux bien sûr pas toucher aux tables)

Merci

[Peut-êtreUneRéponse]

Ma seconde tentative me permet de récupérer les clients sans dossiers mais je n'arrive pas à rajouter le critère de sélection sur l'état du dossier [...]

==> TOTO 2
TITI 0
- ko : compte DOS_1 qui est refusé
- ok : affiche bien TITI qui n'a pas de dossiers

Es-tu sûr de devoir obtenir

Code :

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

1
2
TOTO 1
TITI 0

D'après ton exemple, TOTO a DOS_1 et DOS_2 ouvert (=2)

Ne dois-tu pas plutôt obtenir

Code :

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

1
2
TOTO 2
TITI 0

[dehorter olivier]

Ma seconde tentative me permet de récupérer les clients sans dossiers mais je n'arrive pas à rajouter le critère de sélection sur l'état du dossier :

Cela est tout à fait logique. Si le client n'a pas de dossier, comment pourrait-il y avoir un 'etat de dossier'

Code :

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

1
2
3
4
5
6
7
SELECT CLIENTS.ID, COUNT(DISTINCT ETAT_DOSSIERS.ETAT)
FROM CLIENTS                          
LEFT OUTER JOIN DOSSIERS ON DOSSIERS.CLIENT_ID=CLIENTS.ID    
     LEFT OUTER JOIN ETAT_DOSSIERS ON (                   
       ETAT_DOSSIERS.ID = DOSSIERS.ID   )   
WHERE  ETAT_DOSSIERS.ETAT = 'ouvert'  OR  ETAT_DOSSIERS.ETAT IS NULL                                                   
GROUP BY CLIENTS.ID

[Thomas321]

Peut-êtreUneRéponse :

Oui je suis sûr de vouloir obtenir :

TOTO 1
TITI 0

Tous les dossiers sont créés à l'état "ouvert", ensuite il peuvent changer d'état jusqu'à être fermés, par exemple :
ouvert --> refusé --> fermé
Lorsque l'état change on ne modifie pas la colonne "état" mais on créé un nouvel enregistrement avec le nouvel état, par exemple :

ETAT_DOSSIER | ID | ETAT
---------------------------
| DOS_x | ouvert
| DOS_x | refusé
| DOS_x | fermé

Dans mon exemple DOS_1 a deux enregistrements, un a "ouvert", l'autre à "refusé", son état courant est donc "refusé", je ne veux pas le compter (l'enregistrement avec l'état "ouvert" sert uniquement d'historique)

Je veux donc compter tous les enregistements qui sont à l'état "ouvert" et pour lesquels il n'existe pas d'autres enregistrements pour le même dossier dans un autre état.

[Thomas321]

dehorter olivier :

Ca me semblait clair : je veux récupérer les clients qui n'ont pas de dossiers et ceux qui ont un dossier ouvert. Logiquement je veux donc récupérer l'état du dossier pour les clients qui en ont un.
Je complète donc ma phrase :

Ma seconde tentative me permet de récupérer les clients sans dossiers mais je n'arrive pas à rajouter le critère de sélection sur l'état du dossier pour les clients ayant un dossier ouvert

Sinon je te remercie pour ta solution. J'ai juste dû rajouter des parenthèses pour que ça fonctionne (j'avais d'autres critères que je n'avais pas mis pour simplifier la requête).
Sans parenthèse elle parcourt beaucoup trop d'enregistrements, consomme beaucoup trop de ressources et ne peux donc pas être exécutée (DB2) :

Code :

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

1
2
3
4
5
6
7
8
SELECT CLIENTS.ID, COUNT(DISTINCT ETAT_DOSSIERS.ETAT)
FROM CLIENTS                          
LEFT OUTER JOIN DOSSIERS ON DOSSIERS.CLIENT_ID=CLIENTS.ID    
     LEFT OUTER JOIN ETAT_DOSSIERS ON (                   
       ETAT_DOSSIERS.ID = DOSSIERS.ID   )   
WHERE  (ETAT_DOSSIERS.ETAT = 'ouvert'  OR  ETAT_DOSSIERS.ETAT IS NULL )
AND ...                                                  
GROUP BY CLIENTS.ID

[dehorter olivier]

J'ai juste dû rajouter des parenthèses pour que ça fonctionne (j'avais d'autres critères que je n'avais pas mis pour simplifier la requête).

Et surtout elle ne t'aurait fournit les résultats que tu attentais