MySQL query filtering results

Question

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

mickmackusa · Accepted Answer · 2020-06-03 09:50:10Z

2

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

edited Jun 3, 2020 at 9:50

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

answered Jun 1, 2020 at 10:55

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Lahey Corey Over a year ago

That is a nice aproach. The problem with that query is that it also displays the entires where only Amazon has that ean. The desired result is to display those where 'Amazon' merchant has the lowest price where there is at least one different merchant with the same 'ean'. Check this example. dbfiddle.uk/… The entry ``` ('94919', '10343943708','Amazon', '9999.99') ``` Should not be displayed, since only Amazon has that ean.

forpas Over a year ago

Did you mention this condition: at least one different merchant with the same 'ean' in your question?

Lahey Corey Over a year ago

Sorry:( Looks like I thought I did, but I did not. I will edit it. Thanks

Lahey Corey Over a year ago

Thanks. What if after all the previous filtering, I want to just display the merchant "Amazon" ? Requeriments would be: -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' -Of the result, display only the entries that have Amazon as merchant

forpas Over a year ago

dbfiddle.uk/…

Collectives™ on Stack Overflow

MySQL query filtering results

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related