LEFT OUTER JOIN +Condition

**libuma** · 26/01/2010, 10h56

Bonjour à tous.

Je vous explique mon soucis :

Je désire faire apparaître tous les champs d'une table A et afficher les détails qui leurs correspondent dans la table B.

Je fais donc un left outer join :

Code :

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

1
2
 
select a.id  from tbl_a a left outer join tbl_b b on a.id = b.id  where b.pourcent = (select max(d.pourcent) from tbl_b d where d.id=b.id and b.equi = d.equi );

Ca donne bien le résultat souhaité.
Seulement, dans ce cas ci, si il n'y a pas de b.ID correspondant à a.ID .. a.ID ne s'affichera pas.
Je perds donc des enregistrements.

Donc il faut supprimer la clause WHERE et l'ajouter dans ON :

Code :

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

1
2
 
 select a.id  from tbl_a a left outer join tbl_b b on (a.id = b.id AND b.pourcent = (select max(d.pourcent) from tbl_b d where d.id=b.id and b.equi = d.equi )) ;

Mais alors, j'obtiens :

Code :

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

1
2
 
ORA-01799: une colonne ne peut être jointe extérieurement à une sous-interrogation

J'illustre ici un cas simple mais en réalité je joins 5 ou 6 tables donc j'aimerais que ça reste assez simple avec peu de sous requête,union all ..

Quelqu'un aurait une idée?

Merci d'avance

**Waldar** · 26/01/2010, 11h05

Votre besoin s'imbrique dans tous les sens et en voulant simplifier le contexte vous ne donnez pas toutes les clefs permettant la résolution du problème.

Pouvez-vous décrire ce que vous désirez faire (notamment la partie du WHERE, cruciale comme vous l'avez compris), et l'illustrer par un exemple avec des données ?

**libuma** · 26/01/2010, 11h26

Envoyé par Waldar

Votre besoin s'imbrique dans tous les sens et en voulant simplifier le contexte vous ne donnez pas toutes les clefs permettant la résolution du problème.

Pouvez-vous décrire ce que vous désirez faire (notamment la partie du WHERE, cruciale comme vous l'avez compris), et l'illustrer par un exemple avec des données ?

Alors le but serait par exemple :
[html]
TAB A :

ID
Nom
Prénom

[/html]
TAB B : (ID de tab TAB B.ID = TAB A.ID , TAB B peut contenir plusieurs fois l'id TAB A avec des pourcentages et dates différents)
[html]

ID_B
ID
Pourcentage
DATE_

[/html]

On veut afficher chaque utilisateur avec le pourcentage maximum si il existe au moins un enregistrement dans tab B qui correspond à tab A.
Si il n'y en a pas, on affiche quand même l'utilisateur.

Exemple de données :
TAB A[*]1 DUBOIS JEAN[*]2 DUFION LOUIS[*]3 DUPONT ALAIN

TAB B[*]1 1 25% 15/01/2003[*]2 1 50% 16/01/2002[*]3 3 75% 10/10/1981

RESULTAT[*]DUBOIS JEAN 50% 16/01/2002[*]DUFION LOUIS [*]DUPONT ALAIN 75% 10/10/1981

voilà, j'espère avoir été plus claire

Merci

**teach** · 26/01/2010, 11h28

je te propose ceci:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT A.id  
FROM tbl_a A 
LEFT OUTER JOIN (Select * 
                         From tbl_b b
                         WHERE b.pourcent = (SELECT max(d.pourcent) 
		                           FROM tbl_b d 
		                          WHERE d.id=b.id 
                                                   AND b.equi = d.equi ) 
                       ) B
ON A.id = B.id

**Waldar** · 26/01/2010, 12h01

Essayez ainsi :

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
With TableA AS
(
select 1 as id, 'DUBOIS' as nom, 'JEAN' as prenom from dual union all
select 2      , 'DUFION'       , 'LOUIS'          from dual union all
select 3      , 'DUPONT'       , 'ALAIN'          from dual
)
  ,  TableB AS
(
select 1 as id_B, 1 as id, 25 as prc, to_date('15/01/2003', 'dd/mm/yyyy') as dt from dual union all
select 2        , 1      , 50       , to_date('16/01/2002', 'dd/mm/yyyy')       from dual union all
select 3        , 3      , 75       , to_date('10/10/1981', 'dd/mm/yyyy')       from dual
)
  select a.id, a.nom, a.prenom,
         max(B.prc) as prc_max,
         max(B.dt) keep(dense_rank first order by B.prc desc) as dt
    from TableA a
         left outer join TableB b
           on b.id = a.id
group by a.id, a.nom, a.prenom
order by a.id asc;
 
 ID NOM    PRENOM PRC_MAX DT      
--- ------ ------ ------- ----------
  1 DUBOIS JEAN        50 16/01/2002
  2 DUFION LOUIS                    
  3 DUPONT ALAIN       75 10/10/1981

Attention toutefois à la gestion des égalités.
Que désirez-vous comme résultat si deux lignes de tableB ont le même pourcentage max ?

**libuma** · 26/01/2010, 14h44

Envoyé par Waldar

Essayez ainsi :

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
With TableA AS
(
select 1 as id, 'DUBOIS' as nom, 'JEAN' as prenom from dual union all
select 2      , 'DUFION'       , 'LOUIS'          from dual union all
select 3      , 'DUPONT'       , 'ALAIN'          from dual
)
  ,  TableB AS
(
select 1 as id_B, 1 as id, 25 as prc, to_date('15/01/2003', 'dd/mm/yyyy') as dt from dual union all
select 2        , 1      , 50       , to_date('16/01/2002', 'dd/mm/yyyy')       from dual union all
select 3        , 3      , 75       , to_date('10/10/1981', 'dd/mm/yyyy')       from dual
)
  select a.id, a.nom, a.prenom,
         max(B.prc) as prc_max,
         max(B.dt) keep(dense_rank first order by B.prc desc) as dt
    from TableA a
         left outer join TableB b
           on b.id = a.id
group by a.id, a.nom, a.prenom
order by a.id asc;
 
 ID NOM    PRENOM PRC_MAX DT      
--- ------ ------ ------- ----------
  1 DUBOIS JEAN        50 16/01/2002
  2 DUFION LOUIS                    
  3 DUPONT ALAIN       75 10/10/1981

Attention toutefois à la gestion des égalités.
Que désirez-vous comme résultat si deux lignes de tableB ont le même pourcentage max ?

Bonjour,
J'essaye d'adapter votre solution.. ça fonctionne jusqu'à un certain point.
Imaginons ceci : je rajoute une table C

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
  select a.id, a.nom, a.prenom,
         max(B.prc) as prc_max,
         max(B.dt) keep(dense_rank first order by B.prc desc) as dt
         max(c.date_) ,
         c.info
    from TableA a
         left outer join TableB b
           on b.id = a.id
         left outer join TableC c
           on c.id = a.id
group by a.id, a.nom, a.prenom,c.info
order by a.id asc;

Si je rajoute simplement max(c.date_) je n'ai aucun soucis.
Par contre étant que je demande c.info et que celui ci existe plusieurs fois dans la table, j'ai une redondance enorme et je me retrouve avec beaucoup d'enregistrements en trop.

le c.info doit correspondre au max(c.date_)

ps:En cas de pourcentage max, on ne prendrait que le premier

**Waldar** · 26/01/2010, 15h01

Envoyé par libuma

ps:En cas de pourcentage max, on ne prendrait que le premier

Que signifie le premier ? La date la plus ancienne ? Le plus petit id ?

Pas de soucis pour rajouter des éléments de C, il faut appliquer la même fonction FIRST sur votre élément.
Le mieux serait de compléter votre exemple !

**libuma** · 26/01/2010, 15h32

TAB A :

ID
Nom
Prénom

[/html]
TAB B : (ID de tab TAB B.ID = TAB A.ID , TAB B peut contenir plusieurs fois l'id TAB A avec des pourcentages et dates différents)

ID_B
ID
Pourcentage
DATE_

TAB C :

ID_C
ID
INFO
DATE_

Exemple de données :
TAB A[*]1 DUBOIS JEAN[*]2 DUFION LOUIS[*]3 DUPONT ALAIN

TAB B[*]1 1 25% 15/01/2003[*]2 1 50% 16/01/2002[*]3 3 75% 10/10/1981

TAB C :

1 3 MASTER 04/01/1970
2 3 SLAVE 10/10/1981

RESULTAT[*]DUBOIS JEAN 50% 16/01/2002[*]DUFION LOUIS [*]DUPONT ALAIN 75% 10/10/1981 SLAVE

voilà, j'espère avoir été plus claire

Merci

On se réfaire à la date max de B et l'identifiant de A(qui est = à l'B.ID aussi) pour affichier l'info de C

**Waldar** · 26/01/2010, 15h42

Il suffit de rajouter la date dans la jointure entre B et C :

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
WITH TableA AS
(
SELECT 1 AS id, 'DUBOIS' AS nom, 'JEAN' AS prenom FROM dual union ALL
SELECT 2      , 'DUFION'       , 'LOUIS'          FROM dual union ALL
SELECT 3      , 'DUPONT'       , 'ALAIN'          FROM dual
)
  ,  TableB AS
(
SELECT 1 AS id_B, 1 AS id, 25 AS prc, to_date('15/01/2003', 'dd/mm/yyyy') AS dt FROM dual union ALL
SELECT 2        , 1      , 50       , to_date('16/01/2002', 'dd/mm/yyyy')       FROM dual union ALL
SELECT 3        , 3      , 75       , to_date('10/10/1981', 'dd/mm/yyyy')       FROM dual
)
  , TableC AS
(
select 1 as ID_C, 3 as id, 'MASTER' as info, to_date('04/01/1970', 'dd/mm/yyyy') AS dt FROM dual union ALL
select 2        , 3      , 'SLAVE'         , to_date('10/10/1981', 'dd/mm/yyyy') AS dt FROM dual
)
 SELECT a.id, a.nom, a.prenom,
         max(B.prc) AS prc_max,
         max(B.dt) keep(dense_rank first ORDER BY B.prc DESC) AS dt,
         c.info
    FROM TableA a
         LEFT OUTER JOIN TableB b
           ON b.id = a.id
         LEFT OUTER JOIN TableC c
           ON c.id = b.id
          AND c.dt = b.dt
GROUP BY a.id, a.nom, a.prenom, c.info
ORDER BY a.id ASC;
 
ID  NOM    PRENOM PRC  DT         INFO  
--- ------ ------ ---- ---------- ------
  1 DUBOIS JEAN     50 16/01/2002       
  2 DUFION LOUIS                        
  3 DUPONT ALAIN    75 10/10/1981 SLAVE

**libuma** · 26/01/2010, 15h43

j'ai fait :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT a.id, a.nom, a.prenom,
         max(B.prc) AS prc_max,
         max(B.dt) keep(dense_rank first ORDER BY B.prc DESC) AS dt
         max(c.date_) ,
         max(c.info) keep(dense_rank first ORDER BY c.date_ DESC)
    FROM TableA a
         LEFT OUTER JOIN TableB b
           ON b.id = a.id
         LEFT OUTER JOIN TableC c
           ON (c.id = a.id)
GROUP BY a.id, a.nom, a.prenom,c.info
ORDER BY a.id ASC;

Donc même si j'ai fait un max sur c.info,peu importe la valeur de c.info, j'aurai forcément la valeur qui correspond à la date la plus récente puisque je fais un order by DESC sur c.date_ qui est en max(c.date_)

C'est correct ?

Merci beaucou!

**libuma** · 27/01/2010, 09h20

Bonjour,

Une autre question, est-ce que je ne devrais pas rajouter un OVER(PARTITION) de ce style pour être exact ?

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
 
 
SELECT a.id, a.nom, a.prenom,
         max(B.prc) AS prc_max,
         max(B.dt) keep(dense_rank first ORDER BY B.prc DESC) AS dt
         max(c.date_) ,
         max(c.info) keep(dense_rank first ORDER BY c.date_ DESC) OVER (PARTITION BY a.id order by c.date_ desc)
 
    FROM TableA a
         LEFT OUTER JOIN TableB b
           ON b.id = a.id
         LEFT OUTER JOIN TableC c
           ON (c.id = a.id)
GROUP BY a.id, a.nom, a.prenom,c.info
ORDER BY a.id ASC;

Pour avoir pour chaque utilisateur, le b.prc maximum, le b.dt max qui correspond au b.prc, la c.date_ maximum et la c.info qui lui correspond.

Est-ce que je ne risque pas d'avoir la c.date_ maximum de la table indépendemment de a.id ?

Merci !

**Waldar** · 27/01/2010, 09h38

Je crois que vous avez raté ma dernière réponse hier, postée une minute avant la votre !

**libuma** · 27/01/2010, 09h53

Envoyé par Waldar

Je crois que vous avez raté ma dernière réponse hier, postée une minute avant la votre !

Je m'étais mal exprimé.

c.info n'est pas lié à b.dt
=>

Code :

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

1
2
3
 
 ON c.id = b.id
 AND c.dt = b.dt

N'est pas approprié.

c.info doit être l'information qui correspond à max(c.date_).
Sans condition supplémentaire telle que nous l'avions écrit, il y a plus d'un enregistrement par personne.
Hors ici je ne veux qu'une ligne par personne.
c.info doit être "distinct" en quelque sorte.

**Waldar** · 27/01/2010, 10h22

Comme précédement, essayez d'illustrer par un exemple qui poserait problème !

**libuma** · 27/01/2010, 10h32

[QUOTE=libuma;4946967]TAB A :

ID
Nom
Prénom

[/html]
TAB B : (ID de tab TAB B.ID = TAB A.ID , TAB B peut contenir plusieurs fois l'id TAB A avec des pourcentages et dates différents)

ID_B
ID
Pourcentage
DATE_

TAB C :

ID_C
ID
INFO
DATE_

Exemple de données :
TAB A

1 DUBOIS JEAN
2 DUFION LOUIS
3 DUPONT ALAIN

TAB B

1 1 25% 15/01/2003
2 1 50% 16/01/2002
3 3 75% 10/10/1981

TAB C :

1 3 10000 04/01/1970
2 3 500 10/10/1981
3 2 200 01/01/2009

RESULTAT

DUBOIS JEAN 50% 16/01/2002 NULL NULL
DUFION LOUIS NULL NULL 200 01/01/2009
DUPONT ALAIN 75% 10/10/1981 500 10/10/1981

voilà, j'espère avoir été plus claire

Merci

**Waldar** · 27/01/2010, 10h48

Comme ça ça à l'air de bien fonctionner :

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
WITH TableA AS
(
SELECT 1 AS id, 'DUBOIS' AS nom, 'JEAN' AS prenom FROM dual union ALL
SELECT 2      , 'DUFION'       , 'LOUIS'          FROM dual union ALL
SELECT 3      , 'DUPONT'       , 'ALAIN'          FROM dual
)
  ,  TableB AS
(
SELECT 1 AS id_B, 1 AS id, 25 AS prc, to_date('15/01/2003', 'dd/mm/yyyy') AS dt FROM dual union ALL
SELECT 2        , 1      , 50       , to_date('16/01/2002', 'dd/mm/yyyy')       FROM dual union ALL
SELECT 3        , 3      , 75       , to_date('10/10/1981', 'dd/mm/yyyy')       FROM dual
)
  , TableC AS
(
SELECT 1 AS ID_C, 3 AS id, 10000 AS info, to_date('04/01/1970', 'dd/mm/yyyy') AS dt FROM dual union ALL
SELECT 2        , 3      , 500          , to_date('10/10/1981', 'dd/mm/yyyy')       FROM dual union ALL
SELECT 3        , 2      , 200          , to_date('10/10/2009', 'dd/mm/yyyy')       FROM dual
)
  SELECT a.id, a.nom, a.prenom,
         max(B.prc) AS prc_max,
         max(B.dt)   keep(dense_rank first ORDER BY B.prc DESC) AS dt_B,
         max(c.info) keep(dense_rank first ORDER BY c.dt  DESC) AS info,
         max(c.dt) as dt_C
    FROM TableA a
         LEFT OUTER JOIN TableB b
           ON b.id = a.id
         LEFT OUTER JOIN TableC c
           ON c.id = a.id
GROUP BY a.id, a.nom, a.prenom
ORDER BY a.id ASC;
 
ID	NOM	PRENOM	PRC_MAX	DT_B		INFO	DT_C
1	DUBOIS	JEAN	50	16/01/2002		
2	DUFION	LOUIS				200	10/10/2009
3	DUPONT	ALAIN	75	10/10/1981	500	10/10/1981

D'ailleurs c'est la requête que vous avez proposé, donc bravo, c'est correct !

**libuma** · 28/01/2010, 09h54

En grand merci pour votre suivi et votre rapidité !

C'est bizarre, j'aurais cru logique de devoir utiliser le over(partition..)
Dans quel cas l'utiliseriez-vous ?

Bonne journée.

LEFT OUTER JOIN +Condition

SQL Oracle

Discussions similaires

Partager

Partager