Get parent categories by parent category id

Question

i have the following categories DB structure

+----+---------------+-----------+
| id | category_name | parent_id |
+----+---------------+-----------+
| 1  | Category One  | 0         |
| 2  | Category Two  | 1         |
| 3  | Category Three| 1         |
| 4  | Category Four | 1         |
| 5  | Category Five | 1         |
| 6  | Category Six  | 1         |
+----+---------------+-----------+

I have variable that contain one of child id for example category_id = 4 which have parent_id = 1

I want to get all parent subcategories including it self 4

I dont have parent ID, i have only category_id that have parent_category that i need

What i did so far

SELECT child.*
FROM `ec_categories` AS `child`
LEFT JOIN `ec_categories` AS `parent` ON `child`.`parent_id`=`parent`.`id`
WHERE `child`.`id` = '4'

Is there a way to get them using 1 query?

Does this answer your question? [Get all parents for a child](https://stackoverflow.com/questions/28170635/get-all-parents-for-a-child) — Mark G, Aug 17 '20 at 08:55

score 1 · Answer 1 · answered Aug 17 '20 at 08:33

Suppose you have the following schema:

CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    name varchar(25) NOT NULL, 
    parent_id INT
);
    
INSERT INTO categories (name, parent_id)
VALUES
    ('Cat 1', 0),
    ('Cat 2', 1),
    ('Cat 3', 1),
    ('Cat 4', 2),
    ('Cat 5', 3);

You can then use a recursive common table expression:

WITH RECURSIVE parents AS
  ( SELECT id, name, parent_id
    FROM categories
    WHERE id = 4 -- 4 as an example here
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN parents p
    ON p.parent_id = c.id
  ) 
  
SELECT * FROM parents;

Result:

+----+-------+-----------+
| id | name  | parent_id |
+----+-------+-----------+
|  4 | Cat 4 |         2 |
|  2 | Cat 2 |         1 |
|  1 | Cat 1 |         0 |
+----+-------+-----------+

You'll need a recent-ish version of MariaDB/MySql

score 0 · Answer 2 · answered Aug 17 '20 at 08:25

0

If you dont have problem with inner queries, then you can use

SELECT * FROM categories where parent_id=(SELECT parent_id FROM categories where id=4);

answered Aug 17 '20 at 08:25

Mohammad Anas

320
3
6

Yep, that it, working fine, is this enough optimized, if yes, thank you for your help. – Sebastian Lobont Aug 17 '20 at 08:50
I don't see more optimized solution in this particular case. If anybody has more optimized solution please let me know. For more help see this link https://dev.mysql.com/doc/refman/8.0/en/optimizing-subqueries.html – Mohammad Anas Aug 17 '20 at 09:17

Get parent categories by parent category id

2 Answers2