[Débutant] Jointure ou sous-requête ?

**Domi2** · 17/10/2014, 10h37

Bonjour,

J'avance dans la construction de ma requête (voir discussion précédente).

J'arrive à compter le nombre de logements, en faire la somme, additionner les surfaces et en faire la somme.

Ce que je désire encore améliorer :
- chaque logement a un coût
- le coût se trouve dans une table liée à ma table `LOGEMENT`, `COUT_LOGEMENT`
- pour chaque logement, il y a 1 à n enregistrements, avec une date `DEBUT_PERIODE` distincte.

Je voudrais donc récupérer le coût du logement dans l'enregistrement lié dont la date de début est la plus haute.

Je suis parti dans l'idée de faire une jointure.

Ce que je n'arrive pas à faire, c'est récupérer l'enregistrement désiré à coup sûr.

Car évidemment :

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
SELECT imm.NUMERO_DOSSIER
    , imm.LETTRE_DOSSIER
    , imm.DATE_DEBUT
    , dist.NOM_DISTRICT
    , comm.NOM_COMMUNE
    , statut.TYPE
    , travaux.TYPE
    , COUNT(CASE log.NOMBRE_PIECE WHEN 1 THEN log.NOMBRE_PIECE END) AS 1p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 1 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 1 THEN coutlog.COUT END), 0) AS Cout
    , COUNT(CASE log.NOMBRE_PIECE WHEN 2 THEN log.NOMBRE_PIECE END) AS 2p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 2 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COUNT(CASE log.NOMBRE_PIECE WHEN 3 THEN log.NOMBRE_PIECE END) AS 3p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 3 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COUNT(CASE log.NOMBRE_PIECE WHEN 4 THEN log.NOMBRE_PIECE END) AS 4p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 4 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COUNT(CASE log.NOMBRE_PIECE WHEN 5 THEN log.NOMBRE_PIECE END) AS 5p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 5 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COUNT(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN log.NOMBRE_PIECE END) AS Nb_log
    , COALESCE(ROUND(SUM(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN log.SURFACE_NETTE END), 2), 0) AS Surface
FROM IMMEUBLE AS imm
JOIN DISTRICT AS dist
JOIN COMMUNE AS comm
JOIN TYPE_STATUT_IMMEUBLE AS statut
JOIN TYPE_NATURE_TRAVAUX AS travaux
JOIN LOGEMENT AS log
INNER JOIN COUT_LOGEMENT AS coutlog
ON dist.ID = comm.DISTRICT_ID_FK
    AND comm.ID = imm.COMMUNE_ID_FK
    AND statut.ID = TYPE_STATUT_IMMEUBLE_ID_FK
    AND travaux.ID = TYPE_NATURE_TRAVAUX_ID_FK
    AND log.IMMEUBLE_ID_FK = imm.ID
    AND coutlog.LOGEMENT_ID_FK = log.ID
WHERE log.NOMBRE_PIECE BETWEEN 1 AND 5
GROUP BY imm.NUMERO_DOSSIER
    , imm.LETTRE_DOSSIER
ORDER BY imm.NUMERO_DOSSIER ASC
    , imm.LETTRE_DOSSIER asc
    , coutlog.DEBUT_PERIODE DESC LIMIT 1;

Code :

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

, coutlog.DEBUT_PERIODE DESC LIMIT 1;

ne fonctionne pas.

Je précise que pour le reste, dans la mesure où je précise une date de début période dans la clause WHERE, les résultats semblent cohérents.

Puis-je obtenir le résultat désiré avec une simple jointure ou bien est-ce que je dois passer par une sous-requête ?

Merci d'avance.

**aieeeuuuuu** · 17/10/2014, 10h56

Bonjour,

La date la plus récente, c'est celle pour laquelle il n'en existe pas de plus récente !

vous pouvez donc faire un test de non existence (NOT EXISTS) avec une sous requete dans la clause WHERE.

Une autre solution est une jointure externe avec vérification de NULL pour la table jointe. Schématiquement :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT ...
FROM ...
INNER JOIN COUT_LOGEMENT CL
    ON ...
LEFT JOIN COUT_LOGEMENT CL2
    ON ...
    AND CL2.DEBUT_PERIODE > CL.DEBUT_PERIODE 
 
WHERE ...
 
AND CL2.DEBUT_PERIODE  IS NULL

**kolodz** · 17/10/2014, 11h01

As-tu tenté de ré-construire ta requête en ne conservant que la problématique du dernière en date ?

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 imm.NUMERO_DOSSIER
    , imm.LETTRE_DOSSIER
    , imm.DATE_DEBUT
	, log.NOMBRE_PIECE
	, coutlog.DEBUT_PERIODE
FROM IMMEUBLE AS imm
JOIN LOGEMENT AS log
INNER JOIN COUT_LOGEMENT AS coutlog
ON dist.ID = comm.DISTRICT_ID_FK
    AND log.IMMEUBLE_ID_FK = imm.ID
    AND coutlog.LOGEMENT_ID_FK = log.ID
WHERE log.NOMBRE_PIECE BETWEEN 1 AND 5
GROUP BY imm.NUMERO_DOSSIER
    , imm.LETTRE_DOSSIER
ORDER BY imm.NUMERO_DOSSIER ASC
    , imm.LETTRE_DOSSIER ASC
    , coutlog.DEBUT_PERIODE DESC LIMIT 1;

C'est au niveau de l'ordre d'éxcusion du group by et du order by qu'il y a un problème.
Le group by est réalisé avant l'application du order by et cela systématiquement.
Or d'après ce que j'ai compris tes différentes lignes de coutlog sont fusionnées par le group by.

Personnellement, je vois deux solutions :

Faire un une jointure sur une sous-requête qui trie la table COUT_LOGEMENT.

Réaliser le group by dans une requête englobant l'ensemble.

Cordialement,
Patrick Kolodziejczyk.

**Domi2** · 17/10/2014, 11h24

Alors j'ai déjà essayé la première solution, mais je ne suis pas sûr d'avoir bien compris.

Pas d'erreur, mais la requête tourne, tourne... sans résultat.

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
SELECT imm.NUMERO_DOSSIER
    , imm.LETTRE_DOSSIER
	, imm.DATE_DEBUT
    , dist.NOM_DISTRICT
    , comm.NOM_COMMUNE
    , statut.TYPE
    , travaux.TYPE
    , COUNT(CASE log.NOMBRE_PIECE WHEN 1 THEN log.NOMBRE_PIECE END) AS 1p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 1 THEN log.SURFACE_NETTE END), 2), 0) AS m2
	, COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 1 THEN coutlog2.COUT END), 0) AS Cout
    , COUNT(CASE log.NOMBRE_PIECE WHEN 2 THEN log.NOMBRE_PIECE END) AS 2p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 2 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COUNT(CASE log.NOMBRE_PIECE WHEN 3 THEN log.NOMBRE_PIECE END) AS 3p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 3 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COUNT(CASE log.NOMBRE_PIECE WHEN 4 THEN log.NOMBRE_PIECE END) AS 4p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 4 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COUNT(CASE log.NOMBRE_PIECE WHEN 5 THEN log.NOMBRE_PIECE END) AS 5p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 5 THEN log.SURFACE_NETTE END), 2), 0) AS m2
	, COUNT(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN log.NOMBRE_PIECE END) AS Nb_log
	, COALESCE(ROUND(SUM(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN log.SURFACE_NETTE END), 2), 0) AS Surface
FROM IMMEUBLE AS imm
JOIN DISTRICT AS dist
JOIN COMMUNE AS comm
JOIN TYPE_STATUT_IMMEUBLE AS statut
JOIN TYPE_NATURE_TRAVAUX AS travaux
JOIN LOGEMENT AS log
INNER JOIN COUT_LOGEMENT AS coutlog1
LEFT JOIN COUT_LOGEMENT AS coutlog2
ON dist.ID = comm.DISTRICT_ID_FK
	AND comm.ID = imm.COMMUNE_ID_FK
	AND statut.ID = TYPE_STATUT_IMMEUBLE_ID_FK
	AND travaux.ID = TYPE_NATURE_TRAVAUX_ID_FK
	AND log.IMMEUBLE_ID_FK = imm.ID
    AND coutlog1.LOGEMENT_ID_FK = log.ID
    AND coutlog2.LOGEMENT_ID_FK = log.ID
    AND coutlog2.DEBUT_PERIODE > coutlog1.DEBUT_PERIODE
WHERE log.NOMBRE_PIECE BETWEEN 1 AND 5
	AND coutlog2.DEBUT_PERIODE IS NULL
GROUP BY imm.NUMERO_DOSSIER
    , imm.LETTRE_DOSSIER
ORDER BY imm.NUMERO_DOSSIER ASC
    , imm.LETTRE_DOSSIER asc;

Merci déjà pour ces premières réponses.

**aieeeuuuuu** · 17/10/2014, 11h39

Envoyé par Domi2

Pas d'erreur, mais la requête tourne, tourne... sans résultat.

Pas étonnant, vous avez une série de produits cartésiens...

Il n'y a qu'une seule condition de jointure dans votre requete initiale...

**aieeeuuuuu** · 17/10/2014, 11h45

arfff,

En fait si, mais vous aviez mis toutes les conditions dans la jointure de la dernière table.
En ajoutant la jointure externe, cela vous faisant effectivement un produit cartésien sur toutes les autres tables...

il convient de mettre les conditions de jointure à chaque jointure :

quelque chose comme ceci (à vérifier)

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
 
SELECT imm.NUMERO_DOSSIER
    , imm.LETTRE_DOSSIER
	, imm.DATE_DEBUT
    , dist.NOM_DISTRICT
    , comm.NOM_COMMUNE
    , statut.TYPE
    , travaux.TYPE
    , COUNT(CASE log.NOMBRE_PIECE WHEN 1 THEN log.NOMBRE_PIECE END) AS 1p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 1 THEN log.SURFACE_NETTE END), 2), 0) AS m2
	, COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 1 THEN coutlog2.COUT END), 0) AS Cout
    , COUNT(CASE log.NOMBRE_PIECE WHEN 2 THEN log.NOMBRE_PIECE END) AS 2p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 2 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COUNT(CASE log.NOMBRE_PIECE WHEN 3 THEN log.NOMBRE_PIECE END) AS 3p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 3 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COUNT(CASE log.NOMBRE_PIECE WHEN 4 THEN log.NOMBRE_PIECE END) AS 4p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 4 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COUNT(CASE log.NOMBRE_PIECE WHEN 5 THEN log.NOMBRE_PIECE END) AS 5p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 5 THEN log.SURFACE_NETTE END), 2), 0) AS m2
	, COUNT(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN log.NOMBRE_PIECE END) AS Nb_log
	, COALESCE(ROUND(SUM(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN log.SURFACE_NETTE END), 2), 0) AS Surface
FROM IMMEUBLE AS imm
JOIN COMMUNE AS comm
	ON comm.ID = imm.COMMUNE_ID_FK
JOIN DISTRICT AS dist
	ON dist.ID = comm.DISTRICT_ID_FK
JOIN TYPE_STATUT_IMMEUBLE AS statut
	ON statut.ID = TYPE_STATUT_IMMEUBLE_ID_FK
JOIN TYPE_NATURE_TRAVAUX AS travaux
	ON travaux.ID = TYPE_NATURE_TRAVAUX_ID_FK
JOIN LOGEMENT AS log
	ON log.IMMEUBLE_ID_FK = imm.ID
INNER JOIN COUT_LOGEMENT AS coutlog1
	ON coutlog1.LOGEMENT_ID_FK = log.ID
LEFT JOIN COUT_LOGEMENT AS coutlog2
    ON coutlog2.LOGEMENT_ID_FK = log.ID
    AND coutlog2.DEBUT_PERIODE > coutlog1.DEBUT_PERIODE
WHERE log.NOMBRE_PIECE BETWEEN 1 AND 5
	AND coutlog2.DEBUT_PERIODE IS NULL
GROUP BY imm.NUMERO_DOSSIER
    , imm.LETTRE_DOSSIER
ORDER BY imm.NUMERO_DOSSIER ASC
    , imm.LETTRE_DOSSIER ASC;

**Domi2** · 17/10/2014, 12h06

Je réfléchis mieux l'estomac plein.

J'ai donc commencé par essayer ceci :

Code :

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

AND coutlog2.DEBUT_PERIODE IS NOT NULL

A priori, cela fonctionne, sauf que lorsque je n'ai qu'un enregistrement dans la table `COUT_LOGEMENT`, cela ne remonte pas dans le résultat.

Je continue.

**Domi2** · 17/10/2014, 12h13

Je suis en train de tester la dernière solution de aieeeuuuuu, cela semble fonctionner.

Il faut maintenant que je vérifie quelques résultats.

**Domi2** · 17/10/2014, 12h47

C'est juste parfait

J'ai juste dû changer mon alias pour les coûts.

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 imm.NUMERO_DOSSIER
    , imm.LETTRE_DOSSIER
    , imm.DATE_DEBUT
    , dist.NOM_DISTRICT
    , comm.NOM_COMMUNE
    , statut.TYPE
    , travaux.TYPE
    , COUNT(CASE log.NOMBRE_PIECE WHEN 1 THEN log.NOMBRE_PIECE END) AS 1p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 1 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 1 THEN coutlog1.COUT END), 0) AS Cout
    , COUNT(CASE log.NOMBRE_PIECE WHEN 2 THEN log.NOMBRE_PIECE END) AS 2p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 2 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 2 THEN coutlog1.COUT END), 0) AS Cout
    , COUNT(CASE log.NOMBRE_PIECE WHEN 3 THEN log.NOMBRE_PIECE END) AS 3p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 3 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 3 THEN coutlog1.COUT END), 0) AS Cout
    , COUNT(CASE log.NOMBRE_PIECE WHEN 4 THEN log.NOMBRE_PIECE END) AS 4p
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 4 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 4 THEN coutlog1.COUT END), 0) AS Cout
    , COUNT(CASE log.NOMBRE_PIECE WHEN 5 THEN log.NOMBRE_PIECE END) AS 5p
    , COALESCE(SUM(CASE log.NOMBRE_PIECE WHEN 5 THEN coutlog1.COUT END), 0) AS Cout
    , COALESCE(ROUND(SUM(CASE log.NOMBRE_PIECE WHEN 5 THEN log.SURFACE_NETTE END), 2), 0) AS m2
    , COUNT(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN log.NOMBRE_PIECE END) AS Nb_log
    , ROUND(SUM(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN log.SURFACE_NETTE END), 2) AS Surface
    , SUM(CASE WHEN log.NOMBRE_PIECE BETWEEN 1 AND 5 THEN coutlog1.COUT END) AS Cout
FROM IMMEUBLE AS imm
JOIN COMMUNE AS comm
    ON comm.ID = imm.COMMUNE_ID_FK
JOIN DISTRICT AS dist
    ON dist.ID = comm.DISTRICT_ID_FK
JOIN TYPE_STATUT_IMMEUBLE AS statut
    ON statut.ID = TYPE_STATUT_IMMEUBLE_ID_FK
JOIN TYPE_NATURE_TRAVAUX AS travaux
    ON travaux.ID = TYPE_NATURE_TRAVAUX_ID_FK
JOIN LOGEMENT AS log
    ON log.IMMEUBLE_ID_FK = imm.ID
INNER JOIN COUT_LOGEMENT AS coutlog1
    ON coutlog1.LOGEMENT_ID_FK = log.ID
LEFT JOIN COUT_LOGEMENT AS coutlog2
    ON coutlog2.LOGEMENT_ID_FK = log.ID
    AND coutlog2.DEBUT_PERIODE > coutlog1.DEBUT_PERIODE
WHERE log.NOMBRE_PIECE BETWEEN 1 AND 5
    AND coutlog2.DEBUT_PERIODE IS NULL
GROUP BY imm.NUMERO_DOSSIER
    , imm.LETTRE_DOSSIER
ORDER BY imm.NUMERO_DOSSIER ASC
    , imm.LETTRE_DOSSIER ASC;

Et les deux jointures pour isoler la dernière période, ça m'ouvre plein de perspectives.

Merci beaucoup, c'est top.

**aieeeuuuuu** · 17/10/2014, 13h05

au fait, vous n'aviez pas précisé le SGBD il me semble, et le LIMIT m'avais fait supposer du MySQL

Si ce n'est pas le cas, pour trouver le dernier cout du logement, il serait préférable de passer par des fonctions analytiques.

**Domi2** · 17/10/2014, 13h16

Oui, c'est bien MySQL.

Je sais que cela peut avoir une grande importance, il faut absolument que je m'en souvienne pour les prochaines discussions.

[Débutant] Jointure ou sous-requête ?

Langage SQL

Discussions similaires

Partager

Partager