requête filtrée sur table avec relation plusieurs-à-plusieurs [Résolu]

[nighma]

Bonjour,

Voilà, je dois faire une requête particulière et je ne sais si je peux la faire en une seule.

Voici le contexte :

J'ai 3 tables :

• une table voiture (car)
• une table conducteur (driver)
• une table (plusieurs-à-plusieurs) entre les 2 premières (cardriver)

Dans cette 3ème table, je peux donc retrouver l'historique des conducteurs par voiture avec une date de début et date de fin.

Ma requête est la suivante :

Je dois avoir la liste des tous les véhicules (même sans conducteur) avec s'il existe le nom du dernier conducteur (me basant sur la date de début de la table plusieurs-à-plusieurs)

Pour le moment, j'arrive à ceci :

Code :

1
2
3
4
SELECT car.plate, cardriver.driver_id, driver.name, driver.firstname FROM car
LEFT JOIN cardriver ON cardriver.car_id = car.id
LEFT JOIN driver ON cardriver.driver_id = driver.id
GROUP BY cardriver.car_id

mais je n'arrive pas à lui dire de prendre le dernier conducteur en date.

Pouvez-vous m'aider ?

Merci d'avance

[SQLpro]

Beaucoup d'horreur dans cette requête...
1) pourquou un GROUP BY ??? Pour plomber les performances du SSGBDR parce que vous avez trops de CPU, RAM... ???,
2) pourquoi ne pas mettre des laias de table pour soulager votre code ?
3) pourquoi ne pas avoir mis le même nom de colonne entre la clef primaire de votre table et le clef étrangère représentant cette clef primaire dans une autre table ? (ce sont les mêmes données et même données = même code !!!).
ENfin, pour faire cela il faut soit une sous requ^^ete avec un MAX, soit une fonction de fenêtrage.

A - votre requête récrite :

Code :

1
2
3
4
5
6
SELECT C.plate, CD.driver_id, D.name, D.firstname 
FROM car AS C
       LEFT OUTER JOIN cardriver AS CD
            ON CD.car_id = C.id
       LEFT OUTER JOIN driver AS D
            ON CD.driver_id = D.id

B - solution avec max :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH T AS (
SELECT car_id, MAX(date_fin) AS MD 
FROM   cardriver
GROUP  BY car_id)
SELECT C.plate, D.*
FROM   car AS C
       LEFT OUTER JOIN T 
            ON T.car_id = C.id
       LEFT OUTER JOIN cardriver AS CD
            ON T.car_id = CD.car_id
               AND T.MD = CD.date_fin 
       LEFT OUTER JOIN driver AS D
            ON CD.driver_id = D.id

Enfin : merci de respecter la charte de postage en donnant le DDL de vos table. C'est plus facile de vous aider, surtout avec des noms de colonnes qui sont tous en ID !!!!!!
http://www.developpez.net/forums/a69...gage-sql-lire/

A +

[nighma]

Tout d'abord merci Frédéric.

Je comprends que le code que tu as vu t'as fait bondir mais je le voulais le plus simple possible pour les lecteurs. Je fais encore des erreurs mais je tente de m'améliorer chaque jour. Tes conseils vont déjà m'aider ;-)

Je n'avais pas vu la charte et je m'en excuse. Voici les schémas des tables impliquées.

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
CREATE TABLE IF NOT EXISTS `car` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `plate` varchar(255) collate utf8_unicode_ci NOT NULL DEFAULT '',
  `type` varchar(255) collate utf8_unicode_ci DEFAULT NULL,
  `release_date` date DEFAULT NULL,
  `state` text collate utf8_unicode_ci,
  `maintenance_contract` varchar(255) collate utf8_unicode_ci DEFAULT NULL,
  `leasing_company` varchar(255) collate utf8_unicode_ci DEFAULT NULL,
  `insurance_company` varchar(255) collate utf8_unicode_ci DEFAULT NULL,
  `is_sold` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` int(11) NOT NULL DEFAULT '0',
  `updated_by` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `plate` (`plate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=119 ;
 
CREATE TABLE IF NOT EXISTS `cardriver` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `car_id` bigint(20) NOT NULL DEFAULT '0',
  `driver_id` bigint(20) NOT NULL DEFAULT '0',
  `starts_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `ends_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` int(4) NOT NULL DEFAULT '0',
  `updated_by` int(4) NOT NULL DEFAULT '0',
  PRIMARY KEY  (`id`),
  KEY `car_id_idx` (`car_id`),
  KEY `driver_id_idx` (`driver_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=107 ;
 
CREATE TABLE IF NOT EXISTS `driver` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) collate utf8_unicode_ci NOT NULL DEFAULT '',
  `firstname` varchar(100) collate utf8_unicode_ci NOT NULL DEFAULT '',
  `date_of_birth` date DEFAULT NULL,
  `email` varchar(255) collate utf8_unicode_ci NOT NULL DEFAULT '',
  `address` varchar(255) collate utf8_unicode_ci DEFAULT NULL,
  `zipcode` varchar(255) collate utf8_unicode_ci DEFAULT NULL,
  `city` varchar(255) collate utf8_unicode_ci DEFAULT NULL,
  `country` varchar(255) collate utf8_unicode_ci DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` int(4) NOT NULL DEFAULT '0',
  `updated_by` int(4) NOT NULL DEFAULT '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=41 ;

Je voudrais également préciser que j'utilise MySQL 5 et il n'a pas l'air d'accepter la syntaxe avec le WITH. Y a-t-il une autre méthode ?

Merci

[nighma]

J'ai finalement réussi par trouver la solution grâce à toi et en supprimant la cause WITH que MySQL ne comprend pas :

Code :

1
2
3
4
5
SELECT c.plate, d.name, d.firstname
FROM car AS c
LEFT OUTER JOIN (SELECT car_id, MAX(starts_at) AS md FROM cardriver GROUP BY car_id) AS t ON t.car_id = c.id
LEFT OUTER JOIN cardriver AS cd ON t.car_id = cd.car_id AND t.md = cd.starts_at
LEFT OUTER JOIN driver AS d ON cd.driver_id = d.id

Merci

PS: J'ai parcouru également le début de tes cours et c'est très intéressant. J'aime la façon dont tu expliques simplement les choses.