Jointures, group by et requête imbriquée

**ziyadah** · 17/10/2012, 10h22

Bonjour à tous,

Je suis nouvelle dans ce forum

, et me décide enfin à vous poser la question sur laquelle je bute depuis presque 2 jours...

J'ai une base avec une table CLIENT(IDCLIENT, nom, prenom, adr, cp, ville, pays, ...) et une table RDV_LIGNE(IDRDV_LIGNE, IDCLIENT, date_rdv, heure_rdv, index, inter_rdv_annulé, ...)
Les 2 sont reliées via la clé étrangère IDCLIENT dans RDV_LIGNE.
Un client peut avoir (0,n) RDV_LIGNE; un rdv n'appartient qu'à un seul client.
La rubrique index de RDV_LIGNE me permet d'avoir un n° de rdv croissant (pour faciliter le tri); vous pouvez utiliser la date si vous préférez.

Je sais que ma question a déjà été posée plusieurs fois (et j'ai trouvé grâce à vous un début de réponse), mais j'ai besoin d'aller plus loin que ce que j'ai pu trouver :
Je dois afficher un listing de tous les clients avec les données de leur dernier rdv, selon plusieurs critères sur les 2 tables.

J'ai réussi à avoir la liste du dernier rdv de chaque client avec cette requête :

Code :

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

1
2
3
4
5
 
SELECT 
	rv_max.IDCLIENT, c.nom1cli, rv_max.max_index
FROM 
	CLIENT c INNER JOIN (SELECT IDCLIENT, MAX(INDEX) AS max_index FROM RDV_LIGNE GROUP BY IDCLIENT) rv_max ON rv_max.IDCLIENT=c.IDCLIENT

=> Cette requête me renvoie le bon nb d'enregistrements, et les bonnes valeurs, mais il me manque des infos car comme je dois sortir un listing il me faut toutes les coordonnées du client, ainsi que du rdv.
De plus, j'ai besoin de rajouter des conditions, à la fois sur client et sur rdv, et je suis bloquée par le group by pour les conditions sur le rdv (par exemple, exclusion des rdv annulés)

J'ai également trouvé une autre méthode :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT 
	rv.IDCLIENT, c.nom1cli, c.prenom1cli, c.cp1cli, rv.INDEX, rv.date_dbt_rdv, rv.heure_dbt_rdv
FROM 
	CLIENT c INNER JOIN RDV_LIGNE rv ON c.IDCLIENT=rv.IDCLIENT
WHERE
       rv.INDEX = (SELECT MAX(rv_max.INDEX) FROM RDV_LIGNE rv_max WHERE rv_max.IDCLIENT=rv.IDCLIENT GROUP BY rv_max.IDCLIENT)
       AND rv.inter_rdv_annule=0
       AND rv.DATE_dbt_rdv BETWEEN '20120901' AND '20120930'
       AND c.nompays='FRANCE'
       AND c.dep1cli='32'

=> Cette requête me permet de récupérer toutes les rubriques dont j'ai besoin et je peux même mettre des conditions, donc tout va bien sauf que c'est beaucoup, beaucoup, beaucoup trop long... (même avec les critères de restrictions).

Je ne vous ai pas précisé (et ça risque de ne pas vous plaire), mais je travaille avec Windev et une base HyperFile classic.

Auriez-vous une autre requête à me proposer afin de solutionner mon pb ?

En vous remerciant d'avance !

**CinePhil** · 17/10/2012, 10h48

J'ai réussi à avoir la liste du dernier rdv de chaque client avec cette requête :

Code :

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

1
2
3
4
5
 
SELECT 
	rv_max.IDCLIENT, c.nom1cli, rv_max.max_index
FROM 
	CLIENT c INNER JOIN (SELECT IDCLIENT, MAX(INDEX) AS max_index FROM RDV_LIGNE GROUP BY IDCLIENT) rv_max ON rv_max.IDCLIENT=c.IDCLIENT

Cette requête retourne l'index maxi par client mais elle peut être simplifiée :

Code :

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

1
2
3
4
SELECT c.IDCLIENT, c.nom1cli, MAX(INDEX) AS max_index
FROM CLIENT c
INNER JOIN RDV_LIGNE r ON r.IDCLIENT = c.IDCLIENT
GROUP BY c.IDCLIENT, c.nom1cli

Inutile de faire une sous-requête.

J'ai également trouvé une autre méthode :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT 
	rv.IDCLIENT, c.nom1cli, c.prenom1cli, c.cp1cli, rv.INDEX, rv.date_dbt_rdv, rv.heure_dbt_rdv
FROM 
	CLIENT c INNER JOIN RDV_LIGNE rv ON c.IDCLIENT=rv.IDCLIENT
WHERE
       rv.INDEX = (SELECT MAX(rv_max.INDEX) FROM RDV_LIGNE rv_max WHERE rv_max.IDCLIENT=rv.IDCLIENT GROUP BY rv_max.IDCLIENT)
       AND rv.inter_rdv_annule=0
       AND rv.DATE_dbt_rdv BETWEEN '20120901' AND '20120930'
       AND c.nompays='FRANCE'
       AND c.dep1cli='32'

=> Cette requête me permet de récupérer toutes les rubriques dont j'ai besoin et je peux même mettre des conditions, donc tout va bien sauf que c'est beaucoup, beaucoup, beaucoup trop long... (même avec les critères de restrictions).

Ce qui pose des problèmes de performances ici, c'est la sous-requête corrélée dans le WHERE.

On peut compléter la première requête avec toutes les infos du client dont on a besoin et faire une jointure dessus de cette manière :

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
SELECT rv.IDCLIENT, tmp.nom1cli, tmp.prenom1cli, tmp.cp1cli,
	rv.INDEX, rv.date_dbt_rdv, rv.heure_dbt_rdv
FROM RDV_LIGNE rv
INNER JOIN
(
	SELECT c.IDCLIENT, c.nom1cli, c.prenom1cli, c.cp1cli, c.nompays, c.dep1cli
		MAX(INDEX) AS max_index
	FROM CLIENT c
	INNER JOIN RDV_LIGNE r ON r.IDCLIENT = c.IDCLIENT
	GROUP BY c.IDCLIENT, c.nom1cli, c.prenom1cli, c.cp1cli, c.nompays, c.dep1cli
) tmp
	ON tmp.IDCLIENT = rv.IDCLIENT
	AND tmp.max_index = rv.INDEX
WHERE rv.inter_rdv_annule = 0
	AND rv.DATE_dbt_rdv BETWEEN '20120901' AND '20120930'
	AND tmp.nompays = 'FRANCE'
	AND tmp.dep1cli = '32'

Tu peux même déplacer les conditions du WHERE sur tmp dans la sous-requête, ce qui limitera le nombre de lignes à joindre. Tout dépend si tu as besoin d'une requête générique (avec et sans restriction) ou si tu as toujours besoin de restreindre à un pays et un département :

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
SELECT rv.IDCLIENT, tmp.nom1cli, tmp.prenom1cli, tmp.cp1cli,
	rv.INDEX, rv.date_dbt_rdv, rv.heure_dbt_rdv
FROM RDV_LIGNE rv
INNER JOIN
(
	SELECT c.IDCLIENT, c.nom1cli, c.prenom1cli, c.cp1cli, c.nompays, c.dep1cli
		MAX(INDEX) AS max_index
	FROM CLIENT c
	INNER JOIN RDV_LIGNE r ON r.IDCLIENT = c.IDCLIENT
	WHERE c.nompays = 'FRANCE'
		AND c.dep1cli = '32'
	GROUP BY c.IDCLIENT, c.nom1cli, c.prenom1cli, c.cp1cli, c.nompays, c.dep1cli
) tmp
	ON tmp.IDCLIENT = rv.IDCLIENT
	AND tmp.max_index = rv.INDEX
WHERE rv.inter_rdv_annule = 0
	AND rv.DATE_dbt_rdv BETWEEN '20120901' AND '20120930'

Au passage, tu devrais avoir une table des pays et une clé étrangère référençant l'identifiant du pays dans la table CLIENT.

**ziyadah** · 17/10/2012, 12h30

Envoyé par CinePhil

Cette requête retourne l'index maxi par client mais elle peut être simplifiée :

Code :

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

1
2
3
4
SELECT c.IDCLIENT, c.nom1cli, MAX(INDEX) AS max_index
FROM CLIENT c
INNER JOIN RDV_LIGNE r ON r.IDCLIENT = c.IDCLIENT
GROUP BY c.IDCLIENT, c.nom1cli

Inutile de faire une sous-requête.

En fait, j'avais fait une sous-requête dans l'optique de rajouter les rubriques dont j'avais besoin, ainsi que les critères, mais c'était impossible (en tous cas, je n'y arrivais pas !)

Envoyé par CinePhil

Ce qui pose des problèmes de performances ici, c'est la sous-requête corrélée dans le WHERE.

Bien d'accord !

Envoyé par CinePhil

On peut compléter la première requête avec toutes les infos du client dont on a besoin et faire une jointure dessus de cette manière :

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
SELECT rv.IDCLIENT, tmp.nom1cli, tmp.prenom1cli, tmp.cp1cli,
	rv.INDEX, rv.date_dbt_rdv, rv.heure_dbt_rdv
FROM RDV_LIGNE rv
INNER JOIN
(
	SELECT c.IDCLIENT, c.nom1cli, c.prenom1cli, c.cp1cli, c.nompays, c.dep1cli
		MAX(INDEX) AS max_index
	FROM CLIENT c
	INNER JOIN RDV_LIGNE r ON r.IDCLIENT = c.IDCLIENT
	GROUP BY c.IDCLIENT, c.nom1cli, c.prenom1cli, c.cp1cli, c.nompays, c.dep1cli
) tmp
	ON tmp.IDCLIENT = rv.IDCLIENT
	AND tmp.max_index = rv.INDEX
WHERE rv.inter_rdv_annule = 0
	AND rv.DATE_dbt_rdv BETWEEN '20120901' AND '20120930'
	AND tmp.nompays = 'FRANCE'
	AND tmp.dep1cli = '32'

Super !!!! Merci beaucoup, ça marche impeccable !

C'est là que je vois que Windev m'a fait perdre toutes mes connaissances en SQL...

J'étais incapable de trouver comment écrire cette requête.

Envoyé par CinePhil

Tu peux même déplacer les conditions du WHERE sur tmp dans la sous-requête, ce qui limitera le nombre de lignes à joindre. Tout dépend si tu as besoin d'une requête générique (avec et sans restriction) ou si tu as toujours besoin de restreindre à un pays et un département :

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
SELECT rv.IDCLIENT, tmp.nom1cli, tmp.prenom1cli, tmp.cp1cli,
	rv.INDEX, rv.date_dbt_rdv, rv.heure_dbt_rdv
FROM RDV_LIGNE rv
INNER JOIN
(
	SELECT c.IDCLIENT, c.nom1cli, c.prenom1cli, c.cp1cli, c.nompays, c.dep1cli
		MAX(INDEX) AS max_index
	FROM CLIENT c
	INNER JOIN RDV_LIGNE r ON r.IDCLIENT = c.IDCLIENT
	WHERE c.nompays = 'FRANCE'
		AND c.dep1cli = '32'
	GROUP BY c.IDCLIENT, c.nom1cli, c.prenom1cli, c.cp1cli, c.nompays, c.dep1cli
) tmp
	ON tmp.IDCLIENT = rv.IDCLIENT
	AND tmp.max_index = rv.INDEX
WHERE rv.inter_rdv_annule = 0
	AND rv.DATE_dbt_rdv BETWEEN '20120901' AND '20120930'

En fait, tous mes critères sont des paramètres; j'avais mis ces valeurs pour faire des tests. Mais tout sera paramétrable par l'utilisateur.

Envoyé par CinePhil

Au passage, tu devrais avoir une table des pays et une clé étrangère référençant l'identifiant du pays dans la table CLIENT.

Tu as tout à fait raison, et j'ai bien une table PAYS que j'utilise correctement sur d'autres fichiers.
Seulement, à l'origine, la base a été faite par une personne non-développeur, et qui ne connaissait pas les clés étrangères...
Du coup, il faut que je reprenne le projet en entier pour refaire toutes les requêtes, et vu que c'est un gros projet il y a énormément d'impacts.
Or ma direction a des choses plus importantes à me faire-faire qu'une révision de la base dont elle ne voir pas l'utilité !

(et encore, ce pb là est plus que minime par rapport à d'autres pb conceptuels qui sont vraiment bloquants dans le projet, mais que je ne peux quand même pas corriger car d'autres projets sont toujours prioritaires... mais bon, c'est un autre débat !)

En tous cas, un immense MERCI pour ton aide !!
J'aurais dû poser la question plus tôt !

Très bonne journée à toi

**CinePhil** · 17/10/2012, 15h20

J'aurais dû poser la question plus tôt !

C'est vrai que c'est dommage d'être restée bloquée 2 jours là-dessus !

À tout hasard, si tu connais quelqu'un qui manipule Jasperreports à travers Jaspersoft Studio dans Eclipse, je suis preneur.

**ziyadah** · 17/10/2012, 15h39

Envoyé par CinePhil

C'est vrai que c'est dommage d'être restée bloquée 2 jours là-dessus !

Comme tu dis ! Mais cela ne se reproduira plus maintenant !!

Envoyé par CinePhil

À tout hasard, si tu connais quelqu'un qui manipule Jasperreports à travers Jaspersoft Studio dans Eclipse, je suis preneur.

Je suis désolée, j'aurais aimé pouvoir t'aider en retour, mais malheureusement je ne connais pas cela et ne connais personne qui l'utilise...
Bonne chance quand même !

Jointures, group by et requête imbriquée

Langage SQL

Vue hybride

Discussions similaires

Partager

Partager