2

Assume that I have a article_category tree like this:

GrandPa Cate
|______ Parent Cate 1 (article 1, article2)
|______ Parent Cate 2
|______|_____ Child Cate 1 (article 3, article 4)
|______|_____ Child Cate 2 (article 5)

Tables:

Category

id    | parent_id | title
----------------------------------------
1     | null      | GrandPa Cate
----------------------------------------
2     | 1         | Parent Cate 1
----------------------------------------
3     | 1         | Parent Cate 2
----------------------------------------
4     | 3         | Child Cate 1
----------------------------------------
5     | 3         | Child Cate 2
----------------------------------------

Article

id    | cate_id   | title
----------------------------------------
1     | 2         | article 1
----------------------------------------
2     | 2         | article 2
----------------------------------------
3     | 4         | article 3
----------------------------------------
4     | 4         | article 4
----------------------------------------
5     | 5         | article 5
----------------------------------------

How can I get all 5 articles when select the Granpa Cate?

Nấm Lùn
  • 1,277
  • 6
  • 28
  • 48
  • 3
    You can't easily. MySQL does not support recursive queries, although you might be able to manage recursively calling queries if you write a routine for this query. It is possible using the nested set model but that would require a fairly large change to your table structure. If the number of levels is limited you can code joins to do it though (ie, use a series of LEFT JOINs to the max depth of the tree). – Kickstart Oct 11 '13 at 10:45
  • 2
    http://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes – M Khalid Junaid Oct 11 '13 at 10:51
  • see also http://stackoverflow.com/questions/990291/mysql-multi-level-parent-selection-join-question and http://stackoverflow.com/questions/26734737/multi-level-mysql-query-in-one-select-statement – Mawg says reinstate Monica Nov 25 '15 at 09:52
  • @NấmLùn what was your final solution? – Mawg says reinstate Monica Nov 25 '15 at 09:53

0 Answers0