Mysql query to fetch records which are not matched form both tables

Question

I am new to here. I have two two tables , one is temporary table and another one is main table.Before doing final process user click on refresh button . Then I am populating data into the temporary table and need to display to user if there is any difference between two tables like old value - new value. For this I tried below query but not able get the old value. I am getting as null

Temporay table

+------+--------------+----------------+-----+
| asn        |       ean |   belegnr |quantity|
+------+--------------+----------------+-----+
|  700000845 | 4046228201122| 30059314 |2  |
|  700000845 | 4046228138879| 30059314 |3  |
|  700000845 | 4046228138875| 30059320 |25 |
+------+--------------+----------------+--+--+

Main Table

+------+--------------+----------------+-----+
| asn        |       ean |   belegnr |quantity|
+------+--------------+----------------+-----+
|  700000845 | 4046228201122| 30059314 |5  |
|  700000845 | 4046228138879| 30059314 |3  |
|  700000845 | 4046228138888| 30059320 |25 |
+------+--------------+----------------+--+--+

From both tables I have change in ean field and in quantity field For this I am using this query

SELECT
t2.id,t2.ean,t2.belegnr,t2.`quantity`,t1.`quantity`  FROM
temp_table  t2
LEFT OUTER JOIN details  t1 ON
t1.asn = t2.asn
AND t1.ean = t2.ean
AND t1.belegnr =  t2.belegnr
AND t1.quantity = t2.quantity
WHERE t2.asn = 700000845
AND t2.`belegnr` in(30059314,30059320)
AND t1.id IS NULL

Below is the my expected result

+------+--------------+----------------+-----+---------------------- 
| asn        |       ean |    oldean|       belegnr |quantity|Oldqty|
+------+--------------+----------------+-----+----------------------|
|  700000845 | 4046228201122| 4046228201122 |30059314 |2  |   5     |
|  700000845 | 4046228138875| 4046228138888 |30059320 |25  |  25    |
+------+--------------+----------------+--+--+--------+-------------+

Any help would be greatly appreciated.

Answer 1

From both tables I have change in ean field and in quantity field

To solve THIS task you must use the next query:

SELECT t1.asn, t1.ean, t2.ean oldean, t1.belegnr, t1.quantity, t2.quantity Oldqty
FROM temp_table t1, details t2
WHERE t1.asn = t2.asn
  AND t1.belegnr = t2.belegnr
  AND (    t1.ean != t2.ean
        OR t1.quantity != t2.quantity )

BUT...

Looking at your example data and desured result I see that you want to get the record pairs when ean OR quantity is changed.

You can obtain the shown result from shown source data using the next query:

SELECT t1.asn, t1.ean, t2.ean oldean, t1.belegnr, t1.quantity, t2.quantity Oldqty
FROM temp_table t1, details t2
WHERE t1.asn = t2.asn
  AND t1.belegnr = t2.belegnr
  AND (   (     t1.ean = t2.ean
            AND t1.quantity != t2.quantity )
       OR (     t1.ean != t2.ean
            AND t1.quantity = t2.quantity ))

fiddle

Answer 2

SELECT asn
    FROM (
        SELECT * FROM temp_table
        UNION ALL
        SELECT * FROM details
         )
    GROUP BY asn, ean, belegnr, quantity
    HAVING COUNT(*) = 1;

should give you the asn values that have changed. (Actually it will give 2 copies of them.)

SELECT a.asn,
       b.ean, c.ean AS oldean,
       b.belegnr, c.belegnr AS oldb,
       b.quantity, c.quantity AS old_quantity
    FROM ( SELECT DISTINCT asn FROM ( the-above-query ) AS x ) as a
    JOIN temp_table AS b  USING(asn)
    JOIN details    AS c  USING(asn)

should use that list of asns to find the rest of the columns.

(Provided by OP in a Comment:)

SELECT  a.asn, b.eannumber, c.eannumber AS oldean,b.quantity,
        c.quantity AS oldaty
    FROM  
    (
        SELECT  DISTINCT asn
            FROM  
            (
                SELECT  asn
                    FROM  
                    (
                        SELECT  *
                            FROM  temp_table
                            WHERE  asn =700000845
                              AND  belegnr IN (30059314,30059320)
                            UNION  ALL 
                        SELECT  *
                            FROM  details
                            WHERE  asn =700000845
                              AND  belegnr IN (30059314,30059320) 
                    )a
                    GROUP BY  asn, eannumber, belegnr, quantity
                    HAVING  COUNT(*) = 1
             ) AS X 
    ) AS a
    JOIN  dlnotes_refresh_temp_table AS b USING(asn)
    JOIN  desadv_details AS c USING(asn)