FULL OUTER JOIN (or UNION) on 2 tables

I’m facing a SQL request issue. I’m not a SQL expert and I would like to understand my mistakes. My use case is to get all records of the first table + records of the second table that are not present in the first table. I’ve got 2 tables like this :

First table “T-Finance par jalon ZOHO” (with 20 columns):

Num_Affaire, Nom_Jalon, Montant, ...
21021287,APD,3000
21021287,APS,5000
21021287,DCE,10000

Second table “T-Finance par jalon EVERWIN” (with 20 columns):

Num_Affaire_GX, Phase_GX, Montant_GX, ...
21021287,APS,5000
21021287,DCE,10000
21021287,ACT,50000

Wanted result is :

Num_Affaire, Phase, Montant, ...
21021287,APD,3000
21021287,APS,5000
21021287,DCE,10000
21021287,ACT,50000

So I suppose a full outer join is the solution but I don’t know why it does not work as attended. I tried a lot of things but record (21021287, ACT, 50000) is never present in final result.

here is one of the request I tried:

SELECT *
FROM  "T-Finance par jalon ZOHO" AS  zoho
FULL OUTER JOIN "T-Finance par jalon EVERWIN" gx ON gx.Num_Affaire_GX  = zoho.Num_Affaire
     AND    gx.Phase_GX  = zoho.Nom_Jalon  
WHERE    is_startswith(zoho.Nom_Jalon, 'Validation - ')  = 0

I also tried with a UNION and it works but the problem is I don’t know how to get all others informations (columns) of each row. Because if I add others columns into SELECT statement, UNION will not detect duplicates :

SELECT
         min(mix.Num_Ligne),
         mix.Num_Affaire,
         mix.Phase
FROM (  SELECT
             zoho.Num_Ligne as Num_Ligne,
             to_string(zoho.Num_Affaire) as Num_Affaire,
             to_string(zoho.Nom_Jalon) as Phase
    FROM  "T-Finance par jalon ZOHO" AS  zoho 
    UNION
    SELECT
             gx.Num_Ligne_GX as Num_Ligne,
             to_string(gx.Num_Affaire_GX) as Num_Affaire,
             to_string(gx.Phase_GX) as Phase
    FROM  "T-Finance par jalon EVERWIN" gx 
 
) mix 
WHERE    is_startswith(mix.Phase, 'Validation - ')  = 0
GROUP BY 2,
      3

Thanks for your help.

Answer

I assume you want minimum value of Num_Ligne when matching rows are found. Full join version, provided your DBMS supports least. Otherwise you can do it with a CASE expression.

SELECT coalesce(gx.Num_Affaire_GX, zoho.Num_Affaire) Num_Affaire
     , coalesce(gx.Phase_GX, zoho.Nom_Jalon) Phase
     , least(gx.Num_Ligne, zoho.Num_Ligne) Num_Ligne
FROM  "T-Finance par jalon ZOHO" AS  zoho
FULL OUTER JOIN "T-Finance par jalon EVERWIN" gx ON gx.Num_Affaire_GX  = zoho.Num_Affaire
       AND  gx.Phase_GX  = zoho.Nom_Jalon  
WHERE  is_startswith(coalesce(gx.Phase_GX, zoho.Nom_Jalon), 'Validation - ')  = 0

Advertisement

Answer