Question 113512 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – how to select the lower value product of matching SKU

I have two suppliers where I am trying to filter the products to achieve the following:

  • Select Products from Table1(supplier 1) that are not already present in Table3 and some further filtering on categories (Working)
  • Select Products from Table2 (Supplier 2) that are not already present in Table3 (Working)
  • There are duplicate products from Table1 and Table2. I want to filter these so the resulting table does not have duplicate products but I want to do this by selecting the cheaper supplier rate as the product that ends up in the table. (Stuck on this).

My current SQL query at the moment is the below which does the first two items but I am unsure how to modify to get the third item above working. Any suggestions greatly appreciated.

SELECT Vendor, 
       VendorStockCode AS SKU, 
       StockDescription AS Description, 
       StockAvailable AS Stock 
  FROM Table1 
 WHERE NOT EXISTS (SELECT sku 
                     FROM Table3_product_meta_lookup
                    WHERE Table1.VendorStockCode = Table3_product_meta_lookup.sku) 
   AND PrimaryCategory != 'SERVICES' 
   AND PrimaryCategory != 'WARRANTY'  
   AND cast(DealerEx as decimal(10,2)) <= cast('15000.00' as decimal(10,2))
UNION 
SELECT Manufacture_Name, 
       Manufacture_Code, 
       Short_Description,
       Stock_Qty 
  FROM Table2 
 WHERE NOT EXISTS (SELECT sku 
                     FROM Table3_product_meta_lookup 
                    WHERE Manufacture_Code = Table3_product_meta_lookup.sku)
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    OK, after some experiment, I followed a similar line to what I had and seems to be working. Not sure if it is the most efficient manner but is getting the data I am after. Thank you for those who responded. I have pasted the final query below for reference:

    SELECT Vendor, VendorStockCode AS SKU, 
       StockDescription AS Description, 
       StockAvailable AS Stock 
 FROM Table1 
WHERE NOT EXISTS (
       SELECT sku 
         FROM Table3_product_meta_lookup 
        WHERE Table1.VendorStockCode = Table3_product_meta_lookup.sku ) 
  AND PrimaryCategory != 'SERVICES' 
  AND PrimaryCategory != 'WARRANTY'  
  AND CAST(DealerEx AS DECIMAL(10,2)) <= CAST('15000.00' AS DECIMAL(10,2)) 
  AND NOT EXISTS (
       SELECT Manufacture_Code 
         FROM Table2 
        WHERE VendorStockCode = Manufacture_Code 
          AND CAST(DealerEx AS DECIMAL(10,2)) >= CAST(ExTax AS DECIMAL(10,2))) 
      UNION 
SELECT Manufacture_Name, Manufacture_Code, Short_Description, 
               Stock_Qty 
 FROM Table2 
WHERE NOT EXISTS ( 
        SELECT sku 
          FROM Table3_product_meta_lookup 
         WHERE Manufacture_Code = Table3_product_meta_lookup.sku ) 
  AND NOT EXISTS ( 
         SELECT VendorStockCode 
           FROM Table1 
          WHERE VendorStockCode = Manufacture_Code 
            AND CAST(DealerEx AS DECIMAL(10,2)) < CAST(ExTax AS DECIMAL(10,2)));

  2. 0

    How to calculate supplier rate?
    You want to remove the duplicate products and leave one which is lower in supplier rate?

    Assuming that you’ve already collected all data from table1 and table2 with supplier_rate column and union them into a table ‘table_a’.
    It will be easier to filter the duplicated SKUs with different suppliers and leave on with one supplier with a lower rate.
    The tested query below.

    select vendor,sku,stock from (
select vendor,sku,stock,supplier_rate,rank() over(PARTITION by sku order by supplier_rate) as rk from table_a) pd
where rk = 1;

    The input table data:

    vendorname      sku stock   supplier_rate
vendor1         100 1000    30
Manufacture1    100 2000    40
vendor3         200 1500    50
Manufacture2    300 2000    60
Manufacture3    200 1200    25

    The output table data:

    vendorname      sku stock
vendor1         100 1000
Manufacture3    200 1200
Manufacture2    300 2000
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search