How to enhance MySQL query to get categories and subcategories for a specific store

user_15447786 0
Viewed 26

I am doing the following query to return the categories and subcategories for a specific store

SELECT c1.name, c2.name FROM categories c1
LEFT JOIN categories c2 ON c2.parent_id = c1.id
JOIN store s ON s.category_id = c1.id 
WHERE c1.parent_id = 0
AND s.id = 1

Here is a look of how the tables are structured

categories
id | name  | parent_id
1  | test1 | 0
2  | test2 | 1
3  | test3 | 0
4  | test4 | 1

store
id | category_id
1  | 1
1  | 2
1  | 3

I need the query to return something like

categories | subcategories
test 1     | test2
test 3     | NULL

Currently it's returning test 1 | test 4 as well although store.category_id = 4 does not exist. Can you please advise what am I doing wrong?

mysql
1 Answers

For that you need to select the category_id and use only categoires that are in store id

CREATE TABLE store (
  `id` INTEGER,
  `category_id` INTEGER
);

INSERT INTO store
  (`id`, `category_id`)
VALUES
  ('1', '1'),
  ('1', '2'),
  ('1', '3');
CREATE TABLE categories (
  `id` INTEGER,
  `name` VARCHAR(5),
  `parent_id` INTEGER
);

INSERT INTO categories
  (`id`, `name`, `parent_id`)
VALUES
  ('1', 'test1', '0'),
  ('2', 'test2', '1'),
  ('3', 'test3', '0'),
  ('4', 'test4', '1');

SELECT c1.name, c2.name FROM (categories c1
LEFT JOIN categories c2 ON c2.parent_id = c1.id)
wHERE c1.parent_id = 0
AND (c2.id IN (SELECT `category_id` FROM store WHERE id = 1) OR c2.id iS NuLL)

name  | name 
:---- | :----
test1 | test2
test3 | null

db<>fiddle here

user_51935360
Related