CODEWITHSUNDEEP
phpmysql
Lahey Corey
Lahey Corey

Reputation: 103

MySQL query filtering results

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

Answers (1)

forpas
forpas

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

Related Questions