Reputation: 103
I have a table named pt_products with the fields:
`id` int(6) unsigned NOT NULL,
`ean` bigint(13) unsigned NOT NULL,
`merchant` varchar(64) NOT NULL,
`price` decimal(10,2)NOT NULL,
PRIMARY KEY (`id`)
Example of entries on the table:
INSERT INTO `pt_products` (`id`, `ean`, `merchant`, `price`) VALUES
('1', '884116311874', 'Amazon', '10000.00'),
('2', '884116311874', 'BestBuy', '10999.00'),
('3','884116321378', 'CyberPuerta', '14789.00'),
('4', '884116311875', 'Amazon', '10999.00'),
('5', '884116311875', 'BestBuy', '10000.00');
What I need to do is:
-Group the entries on the table that have the same ean.
-Discard the grouped entries in case none of them have "Amazon" on the merchant
field.
-Discard the grouped entries that have "Amazon" on the merchant
field if the price
field of the one containing "Amazon" as merchant has not the lowest price
field.
-At least one different merchant with the same 'ean' besides 'Amazon'
What I have achieved is to group the entries of the table by ean, but this displays all the entries ordered by ean:
SELECT ean, merchant, price FROM pt_products ORDER BY ean;
I wonter what is the best way to get the desired results. If by using the apprpiate query of by filtering the results from the query using a php loop.
You can test using the example I given above here Thank you
Upvotes: 0
Views: 65
Reputation: 164234
You can group by ean
and set the condition in the having clause:
SELECT ean
FROM pt_products
GROUP BY ean
HAVING MIN(price) = MIN(CASE WHEN merchant = 'Amazon' THEN price END)
AND SUM(merchant <> 'Amazon') > 0
If you want all the rows for the results of the above query, then use also the operator IN
:
SELECT *
FROM pt_products
WHERE ean IN (
SELECT ean
FROM pt_products
GROUP BY ean
HAVING MIN(price) = MIN(CASE WHEN merchant = 'Amazon' THEN price END)
AND SUM(merchant <> 'Amazon') > 0
)
See the demo.
Results:
> id | ean | merchant | price
> ----: | -----------: | :---------- | -------:
> 1 | 884116311874 | Amazon | 10000.00
> 2 | 884116311874 | BestBuy | 10999.00
> 87448 | 10343943704 | Amazon | 3259.00
> 93885 | 10343943704 | Linio | 3999.00
> 94913 | 10343943704 | XtremeTecPc | 3834.00
Upvotes: 2