0

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.

chris85
  • 23,846
  • 7
  • 34
  • 51
Gamer
  • 311
  • 4
  • 17

2 Answers2

1

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
whitwhoa
  • 2,389
  • 4
  • 30
  • 61
  • How can you know that there are 5 levels only? – Harshil Doshi Nov 22 '17 at 19:47
  • 1
    @Harshil I honestly did not take that into consideration. When op stated the structure was `Food>Drinks>Juice>Organic>Grape Juice` I assumed there would only ever be 5 levels. I will see about updating answer with a more dynamic query, as you have a good point – whitwhoa Nov 22 '17 at 19:55
  • 1
    @Gamer I'm not sure if that's how you are expecting the data to be returned or not? Also, will the structure always contain a maximum of 5 categories? Or could the tree have a dynamic number of categories? – whitwhoa Nov 22 '17 at 20:20
1

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

Harshil Doshi
  • 3,497
  • 3
  • 14
  • 37
  • Please try the above answer. – Harshil Doshi Nov 22 '17 at 20:27
  • 1
    What if it's not 'stored in continuity'? Since your comment in my answer I've been pondering this. Would that be possible? If one didn't know how deep the sub-categories went and the data was not stored in continuity...would one be able to obtain the desired dataset? I'm genuinely curious about this as we have run into this structure in the past, but always new how many sub categories there were. If there is a way to obtain them with straight sql not knowing the heirarchy level with the data not being stored in continuity...that would be useful for the future. – whitwhoa Nov 22 '17 at 20:27
  • If data is not stored in continuity then the whole concept of IMMEDIATE `pid` given in question fails. So,it's likely to assume that data stored in continuity based on the question. – Harshil Doshi Nov 22 '17 at 20:31
  • Off course the query that can identify subcategories stored without continuity will be a more generic and more helpful answer but there is no need to make the task/query complex here if the data is stored with such continuity. – Harshil Doshi Nov 22 '17 at 20:35