Jointure avec une seule ligne

[pontus21]

Bonjour,

J'aimerais faire une jointure entre deux tables et n'avoir en retour qu'une seule ligne, même s'il y a plusieurs lignes correspondantes dans la seconde table, et avec une condition pour trouver laquelle retourner.

Pour bien me faire comprendre (car je suis pas sûr d'être très clair ), voici un exemple :

J'ai une table "personnes" et une table "voitures" (qui possède un champ "personne_id", clé étrangère pointant sur personnes.id).
Je voudrais retrouver toutes les personnes présentes dans la base et pour chacune avoir la voiture lui appartenant, avec la date d'achat la plus récente.

Pas sûr que ce soit faisable avec le JOIN seul. Mais sait-on jamais !

Merci d'avance.

[dehorter olivier]

bonjour


non ce n'est pas tres clair

la requete de test, et un exemple de donnees en retour serait le bienvenu


et quel est le SGBD ?

[pontus21]

Ok.
Alors, pas de requêtes car justement je n'ai aucune idée de comment faire.
Le SGBD est MySQL, en version 5.

Mes tables :
Personnes (id, nom, naissance)
Voitures (id, nom, date_achat, personne_id)

J'ai par exemple les données suivantes :

Personne : 1, Pontus, 01/01/1980

Voiture  : 1, C1, 02/05/2011, 1
Voiture  : 2, C2, 03/01/2010, 1

J'aimerais que ma requête me donne la personne 1 (Pontus) avec la voiture 1 (C1), soit la voiture acheté le plus récemment.
Et la même chose pour chaque personne de la table Personnes ...

Est-ce plus clair ?
Comme je ne suis pas sûr que ce que je souhaite soit possible, au pire, j'aimerais n'avoir qu'une ligne par personne, même si cette personne a acheté plusieurs voitures : quel type de jointure utiliser alors ?

[soazig]

Bonjour,
Voilà une requete, elle est un peu compliquée parce que j'ai considéré qu'une personne pouvait acheter deux voitures le même jour, dans ce cas j'ai pris une voiture arbitrairement.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT P.id idPersonne, p.nom NomPersonne, p.naissance, vF.id idVoiture,vF.nom nomVoiture, vf.date_achat
FROM Personnes P
INNER JOIN 
	(SELECT vm.personne_id, max(vm.date_achat) AS date_dernier_achat
		FROM Voiture vm
		GROUP BY vm.personne_id
	) V1
	ON P.id=v1.personne_id
INNER JOIN	
	(SELECT v3.personne_id, date_achat, max(V3.id) AS id_derniere_voiture_achetee
		FROM date_achat v3
		GROUP BY v3.personne_id,date_achat
	) V2
	ON v1.personne_id=V2.personne_id
	AND V1.date_dernier_achat=v2.date_achat
INNER JOIN date_achat VF
	ON VF.personne_id=V2.personne_id
	AND VF.id=id_derniere_voiture_achetee

Cela doit se simplifier énormément avec les fonctions de fenêtrage.
A+
Soazig

[Drizzt [Drone38]]

Avec du fenetrage sous Oracle :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH 
  personne AS (SELECT 1 id, 'P1' nom FROM dual union ALL
              SELECT 2, 'P2' FROM dual),
  voiture AS (SELECT 'C1' voiture, to_date('02/05/2011','DD/MM/YYYY') dte, 1 id_p FROM dual union ALL
              SELECT 'C2', to_date('03/01/2010','DD/MM/YYYY'), 1 FROM dual union ALL
              SELECT 'C4', to_date('02/05/2011','DD/MM/YYYY'), 1 FROM dual union ALL
              SELECT 'C2', to_date('02/05/2010','DD/MM/YYYY'), 2 FROM dual union ALL
              SELECT 'C3', to_date('02/05/2011','DD/MM/YYYY'), 2 FROM dual)
SELECT
    p.nom,
    max(v.voiture) keep(dense_rank first ORDER BY v.dte DESC) voiture
FROM personne p
LEFT JOIN voiture v ON v.id_p = p.id
GROUP BY p.nom;

Résultat:

P1 C4
P2 C3

[Waldar]

Envoyé par Drizzt [Drone38]

Avec du fenetrage sous Oracle

En fait ici, il n'y a pas de fenêtrage, c'est la fonction FIRST sous sa forme agrégée classique. Elle existe aussi avec son extension PARTITION BY, mais pas nécessaire dans ce cas-là.
Je me suis fait avoir aussi il y a quelques temps !
Par contre, l'auteur étant sous MySQL, je doute que ça l'aide vraiment.

[Drizzt [Drone38]]

C'est vrai bonne remarque. J'ai tendance à mettre un peu trop de choses sous l'appelation fenêtrage

[CinePhil]

Quelle est, pour chaque personne, la dernière voiture qu'il a achetée ?

Code :

1
2
3
4
SELECT p.id, MAX(v.date_achat) AS dernier_achat
FROM Personne p
LEFT OUTER JOIN Voiture v ON v.personne_id = p.id
GROUP BY p.id

Quelle est la voiture correspondant à cette date d'achat pour chaque personne ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT tmp.nom AS nom_personne, 
    v1.nom AS nom_voiture,
    v1.date_achat
FROM Voiture v1
INNER JOIN 
(
    SELECT p.id, p.nom, MAX(v.date_achat) AS dernier_achat
    FROM Personne p
    LEFT OUTER JOIN Voiture v ON v.personne_id = p.id
    GROUP BY p.id, p.nom
) tmp 
    ON tmp.id = v1.personne_id
    AND tmp.dernier_achat = v1.date_achat