how to select the lower value product of matching SKU

Spacky001 0
Viewed 26

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)
mariadb mysql
2 Answers

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)))
Spacky0010

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
Sai Ren0
Related