0

Good morning everyone, I hope someone can help me with the following question.

In MySQL I need to compare 2 identical tables filled by different employees and return when there is variation in the records, if there is variation, you must return the "part numbers", "quantity" and know if it belongs to table 1 or table 2. So I create a non-existent column called "TypeTable" but it returns me NULL.

This is query:

SELECT numParte
    ,Cantidad
    ,NULL AS "TypeTable"
FROM (
    SELECT numParte
        ,SUM(Cantidad) AS Cantidad
        ,"TypeTable" AS "Table1"
    FROM eboard.pye_hojadecarga
    WHERE id_chklistemb = 'IDHDC-1-HY'
    GROUP BY numParte

    UNION ALL

    SELECT numParte
        ,SUM(Cantidad) AS Cantidad
        ,"Table2"
    FROM eboard.pye_hojaconfirmacion
    WHERE id_hojadecarga = 'IDHDC-1-HY'
    GROUP BY numParte
    ) tbl
GROUP BY numParte
    ,Cantidad
HAVING count(*) = 1
ORDER BY numParte;

enter image description here

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
Max
  • 19
  • 4
  • a `union all` is NOT comparing the 2 tables - it just combines all rows into one result. Look into a "full outer join" here is a reference: https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql – Paul Maxwell Apr 16 '20 at 06:37
  • union all isn't comparing ... !!! you need full outer join for compare. – Amirhossein Apr 16 '20 at 06:47

1 Answers1

1

Your outer query isn't correct, it specifies NULL as the value to display. Instead simply display that third column:

SELECT numParte
     , Cantidad
     , TypeTable
FROM (
    SELECT numParte
         , SUM(Cantidad) AS Cantidad
         , 'Table1'      AS TypeTable
    FROM eboard.pye_hojadecarga
    WHERE id_chklistemb = 'IDHDC-1-HY'
    GROUP BY numParte

    UNION ALL

    SELECT numParte
         , SUM(Cantidad) AS Cantidad
         ,'Table2'       AS TypeTable
    FROM eboard.pye_hojaconfirmacion
    WHERE id_hojadecarga = 'IDHDC-1-HY'
    GROUP BY numParte
    ) tbl
GROUP BY numParte
       , Cantidad
HAVING count(*) = 1
ORDER BY numParte;
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • woow, thank you very much. Seriously, I broke my head for a long time and couldn't find an answer in the MySQL documentation. – Max Apr 16 '20 at 06:46