UNION sans doublon

[guillaume7]

Bonjour,

Soit les tables :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
T_CLIENT
id_client/nom
-------
1/DUPONT
2/DURAND
3/DUPRES
 
CRD
crdid/crdcustid/crddateattribution
-------
1/1/10-11-2010
2/2/11-11-2010
3/3/15-11-2010
 
T_VPC
vpc_id/id_client,vpc_statut
-------
1/1/acquitte

Je souhaite obtenir la liste des clients qui ont une carte attribuée avant le 15/11/2010 et savoir s'ils ont une VPC ou non

Code :

1
2
DUPONT/10-11-2010/acquitte
DURAND/11-11-2010/

J'obtiens ce résultat en "bricolant" avec deux requetes et une procédure pour supprimer les doublons.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT     T_CLIENT.ID_CLIENT, T_CLIENT.NOM, CRD.CRDDATEATTRIBUTION, ''
FROM T_CLIENT, CRD
WHERE CRD.CRDDATEATTRIBUTION < '10/11/2010'                
    AND CRD.CRDCUSTID=T_CLIENT.ID_CLIENT
UNION
SELECT T_CLIENT.ID_CLIENT, T_CLIENT.NOM, CRD.CRDDATEATTRIBUTION, T_VPC.VPC_STATUT
FROM T_CLIENT, CRD, T_VPC,
WHERE 
    AND CRD.CRDDATEATTRIBUTION < '10/11/2010'                
    AND CRD.CRDCUSTID=T_CLIENT.ID_CLIENT                
    AND CRD.CRDCUSTID=T_VPC.ID_CLIENT                
;

Est-il possible d'obtenir ce résultat sans bricolage ?

Vous remerciant par avance de votre aide et j'utilise ORACLE 10G comme SGBD.

[punkoff]

Bonjour,

Quelle est la relation entre vos table T_CLIENT et CRD ? 1-1 ?

Si ca n'est pas le cas que voulez-vous afficher comme date dans le cas où il y a n résultat pour un même clients ?

Sinon, plus simplement regardez du côté du "LEFT OUTER JOIN" ca devrait résoudre votre problème d'union.

[guillaume7]

Bonjour,

Tout d'abord merci de votre réponse. Au sujet de votre question un client n'a en théorie qu'une seule carte mais en pratique il peut en avoir plusieurs. Tout comme il peut y avoir plusieurs VPC pour une seule carte (une VPC est une télévente qui permet de mettre la carte à jour au moment de sa validation).

Ensuite, il s'agissait bien de LEFT OUTER JOIN pour résoudre mon problème. Ca m'a d'ailleurs permis de comprendre le fonctionnement des jointures externes.

Voici la requête complète (pour le topic j'avais légèrement simplifié les tables) :

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 T_CLIENT.ID_CLIENT, T_CLIENT.NOM, T_CLIENT.PRENOM, T_CLIENT.DATE_NAISSANCE, CRD.CRDDATEATTRIBUTION, 
(
CASE 
  WHEN T_VPC_TRT.VPC_TRT_STATUT = 1 then 'EN BASE'
  WHEN T_VPC_TRT.VPC_TRT_STATUT = 2 then 'ENVOYE' 
  WHEN T_VPC_TRT.VPC_TRT_STATUT = 3 then 'ACQUITTE' 
  WHEN T_VPC_TRT.VPC_TRT_STATUT = 4 then 'EN ERREUR'
  WHEN T_VPC_TRT.VPC_TRT_STATUT = 5 then 'ANNULE'
  WHEN T_VPC_TRT.VPC_TRT_STATUT = 6 then 'ANNULE ET REMBOURSE'
  WHEN T_VPC_TRT.VPC_TRT_STATUT = 8 then 'ANNULE PR COPIE CARTE'
  ELSE 'AUCUNE'
END
) AS VPC_STATUT
FROM T_CLIENT
	INNER JOIN CRD ON T_CLIENT.ID_CLIENT = CRD.CRDCUSTID
	LEFT OUTER JOIN T_VPC ON T_CLIENT.ID_CLIENT = T_VPC.ID_CLIENT
	LEFT OUTER JOIN T_VPC_TRT ON T_VPC.ID_VPC = T_VPC_TRT.ID_VPC
WHERE T_CLIENT.CUSTNUMSCOL IS NOT NULL					-- Vérifie qu'il s'agit d'un scolaire
	AND CRD.CRDSTATUS NOT IN (3,4, 5) 				-- Exclue les cartes en liste noire ou interdite ou détruite
    	AND CRD.CRDDATEATTRIBUTION < '01/05/10'    			-- Cartes créées avt le 01/05/2010
ORDER BY NOM, PRENOM         
;

Il me reste un léger problème que je n'arrive pas à corriger c'est lorsqu'il y a plusieurs VPC pour une même carte, ce qui crée un doublon au niveau de l'id du client dans mon résultat. Je souhaiterais en fait n'avoir que la dernière VPC vendu. Le champs permettant ce filtre se trouve dans la table T_VPC et se nomme VPC_DATE_VENTE. Je ne sais pas comment procéder j'ai essayé une requête imbriquée et un regroupement mais sans succès.

[punkoff]

Bonjour,

Au lieu de ce "case" horrible, ne pouvez vous pas faire une table de description de ces codes ?
Ca vous permettrai de faire une jointure (inner ou outer) afin de retirer les bons libellés directement (et ré-utilisable qui plus est)

Sinon pour la demande, les fonctions de fenêtrage permettent de faire ceci assez facilement (partion by) :
http://sqlpro.developpez.com/article...clause-window/

Mais pour cela il va d'abord falloir déterminer le point suivant :
Comment reconnait-on la dernière vpc ?
Je ne vois pas de date dans votre table vpc.

edit: hmm pardon, j'ai mal lu la fin je vous propose une solution plus tard si quelqu'un ne l'aura pas déjà fait.

[guillaume7]

Je ne fais malheureusement qu'exploiter cette base de données... Impossible donc de créer une table.

La dernière VPC est déterminée par le champs VPC_DATE_VENTE de la table T_VPC.

Merci pour le lien il va me falloir du temps pour absorber son contenu mais ca a l'air très intéressant.

[punkoff]

un autre problème se profile : le cas où il y ait 2 vpc pour une même date si votre champ VPC_DATE_VENTE est de type date et non timestamp.

Là il faudra que vous adaptiez la requête selon d'autres critères.
Ensuite je ne sais pas si vous ne voulez prendre en compte que les vpc inférieures à la date testée pour les cartes ou non (à adapter aussi).

Bref, un exemple un peu plus illustré :

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
 
WITH tmp AS (
SELECT a.id_client, b.vpc_trt_statut, row_number() over(partition BY a.vpc_id ORDER BY b.VPC_DATE_VENTE DESC) AS rnk
FROM T_VPC a
INNER JOIN T_VPC_TRT b ON a.id_vpc = b.idvpc
WHERE b.VPC_DATE_VENTE < '01/05/10')
 
SELECT T_CLIENT.ID_CLIENT, T_CLIENT.NOM, T_CLIENT.PRENOM, T_CLIENT.DATE_NAISSANCE, CRD.CRDDATEATTRIBUTION, 
(
CASE 
  WHEN T_VPC_TRT.VPC_TRT_STATUT = 1 then 'EN BASE'
  WHEN T_VPC_TRT.VPC_TRT_STATUT = 2 then 'ENVOYE' 
  WHEN T_VPC_TRT.VPC_TRT_STATUT = 3 then 'ACQUITTE' 
  WHEN T_VPC_TRT.VPC_TRT_STATUT = 4 then 'EN ERREUR'
  WHEN T_VPC_TRT.VPC_TRT_STATUT = 5 then 'ANNULE'
  WHEN T_VPC_TRT.VPC_TRT_STATUT = 6 then 'ANNULE ET REMBOURSE'
  WHEN T_VPC_TRT.VPC_TRT_STATUT = 8 then 'ANNULE PR COPIE CARTE'
  ELSE 'AUCUNE'
END
) AS VPC_STATUT
FROM T_CLIENT
INNER JOIN CRD ON T_CLIENT.ID_CLIENT = CRD.CRDCUSTID
LEFT OUTER JOIN tmp ON tmp.id_client = T_CLIENT.id_client AND tmp.rnk = 1
WHERE T_CLIENT.CUSTNUMSCOL IS NOT NULL
AND CRD.CRDSTATUS NOT IN (3, 4, 5)
AND CRD.CRDDATEATTRIBUTION < '01/05/10'
ORDER BY NOM, PRENOM ;

Cet exemple ne traite donc pas du cas où il y a plusieurs entrées de VPC dans la même journée pour un client donné, il prend arbitrairement une des VPC du jour le plus proche de votre date butoir.

Vous pouvez changer la clause "row_number() over(partition by a.vpc_id order by b.VPC_DATE_VENTE desc)" par "rank() over(..." ou "dense_rank() over(..." (regardez le lien donné plus haut).

[guillaume7]

Je n'y avais pas pensé mais il est en effet possible qu'il y ait 2 VPC le même jour. Je vais donc comme vous me le conseillez adapter la requête avec un autre critère, à priori l'id de la VPC.

Mon problème est résolu, un grand merci encore pour votre aide et vos conseils.

[punkoff]

euh, j'ai fait une boulette, dans la requête sous le with c'est plutôt comme ça en fait :

Code :

1
2
3
4
5
6
 
WITH tmp AS (
SELECT a.id_client, b.vpc_trt_statut, row_number() over(partition BY a.id_client ORDER BY b.VPC_DATE_VENTE DESC) AS rnk
FROM T_VPC a
INNER JOIN T_VPC_TRT b ON a.id_vpc = b.idvpc
WHERE b.VPC_DATE_VENTE < '01/05/10')

En effet j'avais spécifié de faire le partition by sur l'id_vpc, alors que ce chiffre doit être unique pour un client ...