MySQL remove duplicate records, based on custom condition

Question

I have a table "fb_products" table with the following columns:

CREATE TABLE IF NOT EXISTS `fb_products` (
  `id` int(11) NOT NULL,
  `code` varchar(255) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `in_promo` tinyint(1) NOT NULL DEFAULT '0',
  `original_product_id` int(11) DEFAULT '0',
  .........................................
) ENGINE=InnoDB AUTO_INCREMENT=6254 DEFAULT CHARSET=utf8;

I have a search filter in my site using a SELECT, based on several conditions.

If two products have the same "original_product_id" and one of them has "in_promo" = 1 and the other - "in_promo" = 3, I need to select only the record with "in_promo" = 3. Could this be achieved using MySQL query? Here's a sample SQL, generated by the search filter in my site:

SELECT 
p.id,
.............
(
    SELECT SUM(quantity) FROM `gensoft_availability` WHERE code = p.code
) AS avail,
(SELECT id FROM `fb_products_products_cats_links` WHERE prod_id =p.id AND cat_id='17' AND p.visible=1 LIMIT 1) as rent_product
FROM
fb_products AS p
INNER JOIN fb_products_products_cats_links AS c ON c.prod_id = p.id AND c.cat_id IN (19,21,26,23,22,24,20,18)
LEFT JOIN fb_products_images_bg AS i ON i.item_id = p.id AND i.default_item = '1'
WHERE p.language = 'bg' AND p.archive = 0 ...

fthiella · Accepted Answer · 2015-09-14 10:55:11Z

2

I would use NOT EXISTS:

SELECT * FROM fb_products
WHERE
  in_promo=3
  OR
  NOT EXISTS (
    SELECT * FROM fb_products f
    WHERE
      f.original_product_id=fb_products.original_product_id
      AND f.in_promo=3
  )

You can then add multiple conditions, or use this query as a subquery and join the result with other tables.

Please see a fiddle here.

edited Sep 14, 2015 at 10:55

answered Sep 14, 2015 at 10:49

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

Comments

Nitromoon · Accepted Answer · 2015-09-14 10:46:22Z

1

Add order by promo_id DESC limit 1 to your query

answered Sep 14, 2015 at 10:46

Nitromoon

3881 gold badge2 silver badges11 bronze badges

1 Comment

Георги Банков Over a year ago

LIMIT 1 mean it will select only 1 record and this isn't what I want it to do. Moreover, I do not need ORDER BY, but a custom condition only for in_promo=1 and in_promo=3.

crxy11 · Accepted Answer · 2015-09-14 10:49:55Z

0

You could use SELECT MAX(in_promo),original_product_id FROM fb_products GROUP BY original_product_id .

answered Sep 14, 2015 at 10:49

crxy11

911 silver badge5 bronze badges

2 Comments

Георги Банков Over a year ago

MAX(in_promo) doesn't help me. I may have three records with the same original_product_id, and if one of them is in_promo="1" and the other is in_promo="3", it shouldn't select the one with in_promo="1".

crxy11 Over a year ago

thats exactly what this statement does only the entry with in_promo=3 will be selected

Collectives™ on Stack Overflow

MySQL remove duplicate records, based on custom condition

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related