CODEWITHSUNDEEP
mysql
Gamer
Gamer

Reputation: 311

How to select all the subcategories and their child using parent id?

I am creating a category and subcategory system. Suppose I have a main category such as 'Food'. It has a child such as 'Drinks', Drinks has another child 'Juice', Juice has child 'Organic' and Organic has the child 'Grape Juice'. So basically Food is the main parent and the structure is Food>Drinks>Juice>Organic>Grape Juice. Now I have stored them on the category table like below:

enter image description here

As you can see I have a immediate parent id such as pid. Now how I can select all the category name under the main category Food from this table? I will be given the cat_id 1. So, how I can use this cat_id to track all of the sub categories and their child, sub child etc from the table? What will be the SQL? Can anyone help? Thanks in advance.

Upvotes: 0

Views: 946

Answers (2)

Harshil Doshi
Harshil Doshi

Reputation: 3592

If the whole table strictly follows the format where New Category has ' ' as pid and the whole hierarchy is stored in continuity then following query will work for any number of subcategories:

select 
* from
t
where cat_id >= 1    // Instead of 1, you can put here the passed `cat_id`
and cat_id < (select min(cat_id)
            from t
            where pid = ' ' and cat_id > 1) // Instead of 1, you can put here the passed `cat_id`
;

Click here for DEMO

Upvotes: 1

whitwhoa
whitwhoa

Reputation: 2489

This will select all of the columns related to the ca_tid given in the where clause. I believe that is what you are asking for. If not, please elaborate.

SELECT 
cat_level_1.cat_name AS cat_level_1,
cat_level_2.cat_name AS cat_level_2,
cat_level_3.cat_name AS cat_level_3,
cat_level_4.cat_name AS cat_level_4,
cat_level_5.cat_name AS cat_level_5

FROM your_table AS cat_level_1

JOIN your_table AS cat_level_2
ON cat_level_1.ca_tid = cat_level_2.pid

JOIN your_table AS cat_level_3
ON cat_level_2.ca_tid = cat_level_3.pid

JOIN your_table AS cat_level_4
ON cat_level_3.ca_tid = cat_level_4.pid

JOIN your_table AS cat_level_5
ON cat_level_4.ca_tid = cat_level_5.pid

WHERE cat_level_1.ca_tid = 1

Upvotes: 1

Related Questions