0

I'm working on the Join table with union of 5 different table together

as i have written code for union all table and it working fine without join table

    SELECT  sku,
        sum(units_ordered) as units_ordered
FROM   (
        SELECT sku, units_ordered
        FROM   tbl_sku_units_order 
        UNION ALL
        SELECT sku, units_ordered
        FROM   tbl_sku_units_order_de
        UNION ALL
        SELECT sku, units_ordered
        FROM   tbl_sku_units_order_es
        UNION ALL
        SELECT sku, units_ordered
        FROM   tbl_sku_units_order_fr
        UNION ALL
        SELECT sku, units_ordered
        FROM   tbl_sku_units_order_it
       ) AllMarketplace
group by sku
ORDER BY units_ordered DESC;

and the results are perfect

enter image description here

and got 44 row have returned as you can see

"H-Root-M012S-Black 87" which is correct a total of QTY stock. I need them to join with one of tables

Here is a code

SELECT 
    ls.sku                                  AS list_sku,
    COALESCE(MIN(suo.sku), 'Not Sold')      AS sold_sku,
    COALESCE(SUM(suo.units_ordered), 0)     AS units_ordered
FROM
    (SELECT 
        COALESCE(MIN(suo.sku), 'Not Sold') AS sold_sku,
            suo.units_ordered
    FROM
        tbl_sku_units_order AS suo UNION ALL SELECT 
        COALESCE(MIN(suo.sku), 'Not Sold') AS sold_sku,
            suo.units_ordered
    FROM
        tbl_sku_units_order AS suo UNION ALL SELECT 
        COALESCE(MIN(suo.sku), 'Not Sold') AS sold_sku,
            suo.units_ordered
    FROM
        tbl_sku_units_order AS suo UNION ALL SELECT 
        COALESCE(MIN(suo.sku), 'Not Sold') AS sold_sku,
            suo.units_ordered
    FROM
        tbl_sku_units_order AS suo UNION ALL SELECT 
        COALESCE(MIN(suo.sku), 'Not Sold') AS sold_sku,
            suo.units_ordered
    FROM
        tbl_sku_units_order AS suo) AllMarketplace,
    tbl_list_sku AS ls
        LEFT JOIN
    tbl_sku_units_order AS suo ON suo.sku = ls.sku
GROUP BY ls.sku
ORDER BY units_ordered DESC;

and it's returned fine but the calculation is wrong

enter image description here

as "H-Root-M012S-Black 145" which is totally wrong for a total of QTY stock.

as you can see the difference between the number on 87 and 145

I assume I did put something wrong on the coalesce or sum, I have tried Count but at the end, it return a wrong number as well.

0

got fix it on the "FROM tbl_list_sku AS ls left join()T1 on t1.sku = ls.sku" which it solved the problem and it fixed this issue.

SELECT 
    ls.sku                                  AS list_sku,
    COALESCE(MIN(suo.sku), 'Not Sold')      AS sold_sku,
    COALESCE(SUM(suo.units_ordered), 0)     AS units_ordered
FROM tbl_list_sku AS ls left join
    (SELECT sku,
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order 
        UNION ALL
        SELECT sku,  
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_de
        UNION ALL
        SELECT sku,
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_es
        UNION ALL
        SELECT sku,
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_fr
        UNION ALL
        SELECT  sku,
        COALESCE(MIN(sku), 'Not Sold')      AS sold_sku,
        COALESCE(SUM(units_ordered), 0)     AS units_ordered
        FROM   tbl_sku_units_order_it
    ) as t1 on t1.sku = ls.sku
        LEFT JOIN
    tbl_sku_units_order AS suo ON suo.sku = ls.sku
GROUP BY ls.sku
ORDER BY units_ordered DESC;

and it displayed correct

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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