FULL OUTER JOIN (or UNION) on 2 tables-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

Assuming the following:

Both tables have have the exact same columns
Uniqueness only is guaranteed on the entire row (that is, there is no primary key)
Every row in each table IS unique, duplicates only exists across the row

Your only option is subquery together with DISTINCT.

SELECT DISTINCT * FROM (
    SELECT * FROM "T-Finance par jalon ZOHO"
    UNION 
    SELECT * FROM "T-Finance par jalon EVERWIN"
) tbl