MySQL, PHP – WHERE clause

I create query as below, but I have a problem with WHERE part:

$get_products = "SELECT P.*, C.`category_name`,
                 GROUP_CONCAT(`category_name` SEPARATOR ', ') 
                 AS cat 
                 FROM `products` P 
                 NATURAL JOIN `categories` C 
                 NATURAL JOIN `product_to_categories` 
                 WHERE FIND_IN_SET(`category_id`, '$answers')
                 GROUP BY `product_name` 
                 ORDER BY count(C.`category_id`) DESC";

So if I have products i.e.: DB Fiddle

APPLE belonging to the categories: FRUIT, GREEN, ROUND
WHEEL belonging to the categories: MACHINES, BLACK, ROUND
BANANA belonging to the categories: FRUIT, YELLOW, CUBOID
PHONE belonging to the categories: ELECTRONICS, BLACK, CUBOID
TOMATO belonging to the categories: ROUND, VEGETABLE, RED
PIZZA belonging to the categories: ROUND, COLOURFUL, DISH

and select categories: ROUND, FRUIT, GREEN, MACHINES, VEGETABLE

thanks to FIND_IN_SET I will receive:

    +--------------+---------------------+
    | product_name | cat                 |
    +--------------+---------------------+
    | APPLE        | FRUIT, GREEN, ROUND |
    +--------------+---------------------+
    | TOMATO       | VEGETABLE, ROUND    |
    +--------------+---------------------+
    | WHEEL        | MACHINES, ROUND     |
    +--------------+---------------------+
    | BANANA       | FRUIT               |
    +--------------+---------------------+
    | PIZZA        | ROUND               |
    +--------------+---------------------+

Now I am trying to set one of categories mandatory, so it should work like this:

I am selecting products with categories ROUND, FRUIT, GREEN, MACHINES, VEGETABLE.

let say that ROUND is the main (obligatory) category and I should receive:

 +--------------+---------------------+
 | product_name | cat                 |
 +--------------+---------------------+
 | APPLE        | FRUIT, GREEN, ROUND |
 +--------------+---------------------+
 | TOMATO       | VEGETABLE, ROUND    |
 +--------------+---------------------+
 | WHEEL        | ROUND, MACHINES.    |
 +--------------+---------------------+

So from all the products belonging to the ROUND category, I am also looking for FRUIT, GREEN, MACHINES, VEGETABLES.

I tried this clause:

    WHERE (category_id IN ('$main_category')
          AND (FIND_IN_SET(category_id, '$answers')));

It is almost working. Almost because I am loosing ORDER BY count and it returns also PIZZA which belongs to ROUND but not to any of other categories.

Thanks!

CREATE TABLE products (
  `product_id` INTEGER NOT NULL PRIMARY KEY,
  `product_name` VARCHAR(31)
);

INSERT INTO products
  (`product_id`, `product_name`)
VALUES
  ('1', 'APPLE'),
  ('2', 'WHEEL'),
  ('3', 'BANANA'),
  ('4', 'PHONE'),
  ('5', 'TOMATO'),
  ('6', 'PIZZA');

CREATE TABLE categories (
  `category_id` INTEGER,
  `category_name` VARCHAR(31)
);

INSERT INTO categories
  (`category_id`, `category_name`)
VALUES
  ('1', 'FRUIT'),
  ('2', 'GREEN'),
  ('3', 'ROUND'),
  ('4', 'MACHINES'),
  ('5', 'BLACK'),
  ('6', 'YELLOW'),
  ('7', 'CUBOID'),
  ('8', 'ELECTRONICS'),
  ('9', 'DISH'),
  ('10', 'VEGETABLE'),
  ('11', 'RED'),
  ('12', 'COLORFUL');



CREATE TABLE product_to_categories (
  `relation_ID` INTEGER,
  `product_ID` INTEGER,
  `category_ID` INTEGER
);

INSERT INTO product_to_categories
  (`relation_ID`, `product_ID`, `category_ID`)
VALUES
  ('1', '1', '1'),
  ('2', '1', '2'),
  ('3', '1', '3'),
  ('4', '2', '4'),
  ('5', '2', '5'),
  ('6', '2', '3'),
  ('7', '3', '1'),
  ('8', '3', '6'),
  ('9', '3', '7'),
  ('10', '4', '5'),
  ('11', '4', '8'),
  ('12', '5', '3'),
  ('13', '5', '10'),
  ('14', '5', '11'),
  ('15', '6', '3'),
  ('16', '6', '9'),
  ('17', '6', '12');

Answer

E.g.:

SELECT p.*
     , GROUP_CONCAT(COALESCE(c2.category_name,c1.category_name)) name 
  FROM products p 
  JOIN product_to_categories pc1 
    ON pc1.product_id = p.product_id 
  JOIN categories c1 
    ON c1.category_id = pc1.category_id 
  JOIN product_to_categories pc2
    ON pc2.product_id = pc1.product_id
  JOIN categories c2
    ON c2.category_id = pc2.category_id
 WHERE c1.category_name = 'round'
   AND c2.category_name IN ('ROUND','FRUIT', 'GREEN', 'MACHINES', 'VEGETABLE')
 GROUP 
    BY p.product_id
HAVING COUNT(*) > 1 ;

Advertisement

Answer