CODEWITHSUNDEEP
mysqlsql
RedGiant
RedGiant

Reputation: 4748

Compare the lowest values from two tables in mysql

Fiddle Example

Is there anyway to compare the product prices from tables user_price and retailer_price and find out which ones are the lowest and match the target prices from table target_price?

Here's the result I'm trying to get:

   PRODUCT_ID   SELLER  Lowest_Price /* lower than target price */
   1            Amazon  90                      
   2            Paul    120

I am using UNION ALL to get a similar result, but I want to directly compare the prices from user_price and retailer_price so I don't need to have extra NULL columns. I know it'd be easier if the user prices and retailer prices were stored in the same table, but in my actual situation user_price also has other different values to store so I have to go with the table schema. Can anyone point me in the right direction?

SELECT tp.product_id,up.user AS seller,
NULL AS merchant,NULL AS merchant_lowest_price,
up.price AS seller_lowest_price

FROM target_price tp  
INNER JOIN user_price up ON up.product_id = tp.product_id 
WHERE tp.target_price >= up.price

UNION ALL

SELECT tp.product_id,NULL AS seller,NULL AS seller_lowest_price,
rp.retailer,rp.price AS retailer_lowest_price
FROM target_price tp 
INNER JOIN retailer_price rp 
ON rp.product_id = tp.product_id
WHERE tp.target_price >= rp.price

Example Table Schema:

CREATE TABLE user_price
    (`product_id` int,`user` varchar(30),`price` int)
;

INSERT INTO user_price
    (`product_id`,`user`,`price`)
VALUES
    (1,'Tom',200),
    (1,'Sally',120),
    (2,'Peter',150),
    (2,'Paul',120)

;

CREATE TABLE retailer_price
    (`product_id` int,`retailer` varchar(30),`price` int)
;

INSERT INTO retailer_price
    (`product_id`,`retailer`,`price`)
VALUES
    (1,'Amazon',90),
    (2,'Target',400)

;

CREATE TABLE target_price
    (`product_id` int,`target_price` int)
;

INSERT INTO target_price
    (`product_id`,`target_price`)
VALUES
    (1,100),
    (2,130)

;

Upvotes: 1

Views: 156

Answers (1)

radar
radar

Reputation: 13425

you can do union of the retailer and user price tables and the do a join with target price table.

In case there are multiple retailers or users that have lower price than target price, all will be listed. If you want further lowest among them then you can use group by and get min price.

SQL Fiddle

SELECT tp.product_id,  up.seller , up.price as lowest_price
FROM target_price tp
join ( select price, retailer as seller, product_id
       from retailer_price
       union
       select price, user as seller,  product_id 
       from user_price
     ) up
on tp.product_id = up.product_id
and tp.target_price >= up.price

Upvotes: 1

Related Questions