Une requête avec des jointures externes

**Sopra** · 30/07/2009, 10h37

Bonjour à tous,

Je n'arrive pas à construire correctement une requête SQL qui n'a pourtant pas l'air bien compliquée...
Voici déjà un aperçu des tables :

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
CREATE TABLE clubs (
	id INTEGER NOT NULL, 
	num INTEGER, 
	nom VARCHAR(100), 
	effectif INTEGER, 
	PRIMARY KEY (id)
)
CREATE TABLE manif (
	id INTEGER NOT NULL, 
	id_organisateur INTEGER, 
	nom VARCHAR(100), 
	date DATE, 
	route BOOLEAN, 
	vtt BOOLEAN, 
	CP BOOLEAN, 
	PRIMARY KEY (id), 
	 FOREIGN KEY(id_organisateur) REFERENCES clubs (id), 
	 CHECK (route = 1 OR vtt = 1)
)
CREATE TABLE particip (
	id_manif INTEGER, 
	id_club INTEGER, 
	nbre INTEGER, 
	type VARCHAR(5)
)

Ensuite un jeu d'essais :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
INSERT INTO clubs (num, nom, effectif) VALUES (7034, 'Club 1', 5)
INSERT INTO clubs (num, nom, effectif) VALUES (1056, 'Club 2', 10)
INSERT INTO clubs (num, nom, effectif) VALUES (2021, 'Club 3', 15)
 
INSERT INTO manif (id_organisateur, nom, date, route, vtt, CP) VALUES (1, 'Rando du Port', '2009-06-28', 1, 0, 1)
INSERT INTO manif (id_organisateur, nom, date, route, vtt, CP) VALUES (3, 'Une rando quelconque', '2009-06-29', 1, 1, 1)
INSERT INTO manif (id_organisateur, nom, date, route, vtt, CP) VALUES (2, 'Une autre rando quelconque', '2009-06-30', 0, 1, 1)
 
INSERT INTO particip (id_manif, id_club, nbre, type) VALUES (2, 1, 5, 'route')
INSERT INTO particip (id_manif, id_club, nbre, type) VALUES (2, 2, 5, 'route')
INSERT INTO particip (id_manif, id_club, nbre, type) VALUES (2, 2, 5, 'vtt')

Je dois bâtir une requête qui retourne les participants à une manifestation.
Actuellement, je liste les manifs ainsi :

Code :

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

1
2
3
4
5
SELECT manif.id AS manif_id, manif.id_organisateur AS manif_id_organisateur, manif.nom AS manif_nom, manif.date AS manif_date, manif.route AS manif_route, manif.vtt AS manif_vtt, manif."CP" AS "manif_CP", clubs.id AS clubs_id, clubs.num AS clubs_num, clubs.nom AS clubs_nom, clubs.effectif AS clubs_effectif, particip.id_manif AS particip_id_manif, particip.id_club AS particip_id_club, particip.nbre AS particip_nbre, particip.type AS particip_type, sum(particip.nbre) AS participants
FROM manif
JOIN clubs ON clubs.id = manif.id_organisateur
LEFT OUTER JOIN particip ON particip.id_manif = manif.id 
WHERE manif.date > '2009-01-01' AND manif.date < '2009-12-31' GROUP BY manif.id ORDER BY clubs.num

Maintenant, pour _une_ manifestation (définie par son id) j'aimerais avoir la liste des clubs avec leur éventuels participants (en route et vtt). C'est à dire un résultat qui ressemble (certaines colonnes ayant été retirées) à ceci pour la manif 2 :

Code :

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

1
2
3
4
clubs_id|clubs_effectif|particip_route|particip_vtt
    1   |      5       |       5      |       0
    2   |     10       |       5      |       5
    3   |     15       |       0      |       0

Note : Ces requêtes ne sont pas écrites "à la main", elles sont construites par l'ORM SqlAlchemy (pour SQLite) en Python.

Merci d'avance.

**skuatamad** · 30/07/2009, 11h17

Envoyé par Sopra

Note : Ces requêtes ne sont pas écrites "à la main", elles sont construites par l'ORM SqlAlchemy (pour SQLite) en Python.

Alors je ne sais pas si ça va correspondre, mais essaie :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT c.id AS clubs_id,
       c.effectif AS clubs_effectif,
       (case when p.type='route' then p.nbre end) as participe_route,
       (case when p.type='vtt' then p.nbre end) as participe_vtt
FROM manif m
JOIN clubs c ON c.id = m.id_organisateur
LEFT OUTER JOIN particip p ON p.id_manif = m.id
WHERE m.date > '2009-01-01' AND m.date < '2009-12-31'
ORDER BY c.num

PS : D'après ton exemple je ne vois pas l'intérêt d'une somme, mais tu en auras peut être besoin pour le cas réel, donc au cas où, un peu de lecture sur le GROUP BY avec mysql même si je trouve ce laxisme syntaxique dangereux.

**Sopra** · 30/07/2009, 12h53

Envoyé par skuatamad

Alors je ne sais pas si ça va correspondre, mais essaie :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT c.id AS clubs_id,
       c.effectif AS clubs_effectif,
       (case when p.type='route' then p.nbre end) as participe_route,
       (case when p.type='vtt' then p.nbre end) as participe_vtt
FROM manif m
JOIN clubs c ON c.id = m.id_organisateur
LEFT OUTER JOIN particip p ON p.id_manif = m.id
WHERE m.date > '2009-01-01' AND m.date < '2009-12-31'
ORDER BY c.num

PS : D'après ton exemple je ne vois pas l'intérêt d'une somme, mais tu en auras peut être besoin pour le cas réel, donc au cas où, un peu de lecture sur le GROUP BY avec mysql même si je trouve ce laxisme syntaxique dangereux.

Merci pour l'idée du CASE ... END.
Je n'arrive cependant toujours pas à obtenir ce que je veux. J'obtiens toujours deux lignes :

Code :

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

1
2
3
route|vtt
  X  | 0
  0  | Y

à fusionner en :

Code :

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

1
2
route|vtt
  X  | Y

P.S. : Oui le group by j'en aurais besoin. Merci pour la lecture

**skuatamad** · 30/07/2009, 12h56

Ah ben oui en fait il faut une somme

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT c.id AS clubs_id,
       c.effectif AS clubs_effectif,
       sum(case when p.type='route' then p.nbre end) AS participe_route,
       sum(case when p.type='vtt' then p.nbre end) AS participe_vtt
FROM manif m
JOIN clubs c ON c.id = m.id_organisateur
LEFT OUTER JOIN particip p ON p.id_manif = m.id
WHERE m.date > '2009-01-01' AND m.date < '2009-12-31'
group by c.id AS clubs_id, c.effectif AS clubs_effectif
ORDER BY c.num

**Sopra** · 30/07/2009, 17h28

Par-fait.
Finalement ma requête ressemble à :

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
SELECT clubs.id AS clubs_id,
       clubs.num AS clubs_num,
       clubs.nom AS clubs_nom,
       clubs.effectif AS clubs_effectif,
       clubs.id_ville AS clubs_id_ville,
       villes.id AS villes_id,
       villes.cp AS villes_cp,
       villes.nom AS villes_nom,
       villes.id_dep AS villes_id_dep,
       villes.lat AS villes_lat,
       villes.lon AS villes_lon,
       particip.id_manif AS particip_id_manif,
       particip.id_club AS particip_id_club,
       particip.nbre AS particip_nbre,
       particip.type AS particip_type,
       nbre.route AS nbre_route,
       nbre.vtt AS nbre_vtt,
       nbre.id_club AS nbre_id_club 
FROM clubs
       JOIN villes
              ON clubs.id_ville = villes.id
       LEFT OUTER JOIN particip
              ON particip.id_manif = 1
       LEFT OUTER JOIN (
              SELECT
              sum(
                     CASE WHEN (particip.type = 'route') THEN particip.nbre ELSE 0 END
              ) AS route,
              sum(
                     CASE WHEN (particip.type = 'vtt') THEN particip.nbre ELSE 0 END
              ) AS vtt,
              particip.id_club AS id_club 
              FROM particip 
              WHERE
                     particip.id_manif = 1
              GROUP BY particip.id_club
       ) AS nbre
              ON nbre.id_club = clubs.id
       GROUP BY clubs.id
       ORDER BY villes.id_dep, clubs.num

Merci en tout cas, sans toi je n'aurais jamais pensé au SUM(CASE ...)

Une requête avec des jointures externes

Langage SQL

Discussions similaires

Partager

Partager