LEFT OUTER JOIN +Condition

Version imprimable

26/01/2010, 10h56
libuma

LEFT OUTER JOIN +Condition
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:

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:

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:

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 :mrgreen:
26/01/2010, 11h05
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 ?
26/01/2010, 11h26
libuma
Citation:

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

je te propose ceci:

Code:

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

Essayez ainsi :

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
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 ?

Citation:

Envoyé par Waldar

Essayez ainsi :

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
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:

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

26/01/2010, 15h01
Waldar

Citation:

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 !
26/01/2010, 15h32
libuma
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

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

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
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

j'ai fait :
Code:

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!

Bonjour,

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

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 !

27/01/2010, 09h38
Waldar

Je crois que vous avez raté ma dernière réponse hier, postée une minute avant la votre !
27/01/2010, 09h53
libuma
Citation:

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:

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.
27/01/2010, 10h22
Waldar

Comme précédement, essayez d'illustrer par un exemple qui poserait problème !
27/01/2010, 10h32
libuma
[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

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

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
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 !

28/01/2010, 09h54
libuma

En grand merci pour votre suivi et votre rapidité ! :ccool:

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

Bonne journée.