Jointures multiples

Version imprimable

Jointures multiples

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
People
 
PersonID  |  Name
------------------------
    1     |  BOB
    2     |  JOE
    3     |  ANDY
 
Oranges
 
OrangeID  |  PersonID  |  TimeEntered
-------------------------------------------
    1     |     1      |  10-01-08 9:30:00
    2     |     1      |  10-01-08 9:31:00
    3     |     2      |  10-01-08 9:31:00
    4     |     3      |  10-01-08 9:33:00
 
 
Lemons
 
LemonID   |  PersonID  |  TimeEntered
-------------------------------------------
    1     |     3      |  10-01-08 9:30:00
    2     |     3      |  10-01-08 9:31:00
    3     |     3      |  10-01-08 9:31:00
    4     |     1      |  10-01-08 9:33:00

je veut avoir un résultat comme ça:

Code:

1
2
3
4
5
 NAME   | Oranges |  Lemons
-----------------------------
BOB    |    2    |    1
JOE    |    1    |    0
ANDY   |    1    |    3

Je ne saurais que trop vous conseiller d'aller lire une documentation sur les jointures ...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT People.Name, RqOranges.nb  AS Oranges, RqLemons.nb  AS Lemons
  FROM       People
          INNER JOIN
             (SELECT count(Oranges.PersonID)  AS nb, a.PersonID
                FROM people a LEFT OUTER JOIN Oranges
                        ON (a.PersonID = Oranges.PersonID)
               GROUP BY a.PersonID) RqOranges
          ON (People.PersonID = RqOranges.PersonId)
       INNER JOIN
          (SELECT count(Lemons.PersonID)  AS nb, a.PersonID
             FROM people a LEFT OUTER JOIN Lemons
                     ON (a.PersonID = Lemons.PersonID)
            GROUP BY a.PersonID) RqLemons
       ON (People.PersonID = RqLemons.PersonId)

Bon courage

21/05/2008, 08h45
rsc

Si tu nous dis ce que tu as essayé, on est prêts à t'aider, c'est le but du forum :lol:
21/05/2008, 09h02
Chtulus

Pourquoi 2 messages identiques ??? 8O

Quelque chose comme :

Code:

1
2
3
4
5
6
7
8
9
SELECT P.Name, 
       COUNT(O.Oranges) AS Oranges,
       COUNT(L.Lemons) AS Lemons
FROM   People AS P
       LEFT OUTER JOIN Oranges AS O
            ON P.PersonID = O.PersonID
       LEFT OUTER JOIN Lemons AS L
            ON P.PersonID = L.PersonID
GROUP  BY P.Name

Merci de poster le DDL de vos tables ainsi qu'un jeu d'essais sous forme INSERT !
http://www.developpez.net/forums/ann....php?f=37&a=69

A +

21/05/2008, 13h08
al1_24

Ici : Cours sur les Jointures en SQL