0

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.

  • Is asn the PRIMARY KEY in each table? Please provide SHOW CREATE TABLE. – Rick James Feb 6 at 3:05
  • asn is not primary key. asn is the common value in both tables. – user3408779 Feb 6 at 3:07
  • In temporay table at the time when user click on refresh button then I am populating data from other source . After that the temp table data will be flushed. – user3408779 Feb 6 at 3:09
  • From the above join I provided I am getting values like in screenshot. In place of old values I am getting null. prntscr.com/mh7ok9 – user3408779 Feb 6 at 3:11
  • If asn is not the PK, how do I know how to pair up the rows?? – Rick James Feb 6 at 3:18
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

  • awesome, This is working like expected.. Thank you very much man. – user3408779 Feb 6 at 5:46
0
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)
  • 1
    @user3408779 - What did you get from the first SELECT? I am assuming you match up the tables on just asn. If that is wrong, then the first one needs to have more items, and the USINGs in the second needs more. – Rick James Feb 6 at 3:38
  • 1
    The first pair of lines shows that eannumber changed; the second pair show that quantity changed. That's what your desired result is, except for layout. – Rick James Feb 6 at 3:42
  • 1
    I changed the second select to get closer to your format. – Rick James Feb 6 at 3:44
  • 1
    @user3408779 - So are you happy now? – Rick James Feb 6 at 3:45
  • 1
    And, if it is not quite correct, can do you understand the steps (albeit rather lengthy steps) I took to get close to the answer? – Rick James Feb 6 at 3:47

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.