query for selectiong parents and child

Question

I am having one category table where i want to give category for posts

post_category(cat_id,cat_name,parent_id,cat_count)

cat_id    cat_name       parent_id       cat_count
   1           C            NULL           300 
   2         Pointers        1             100
   3         Structures      1             200
   4          Java          NULL           700    
   5         Exceptions      5             200
   6         Threading       5             300
   7         Thread Priority 6             200

here cat_id is primary key and parent id for root elenemts are null and for child parent id is any valid cat_id. I want to select each parent and its child details. as follows

      C
         Pointers
         Structures    

      Java
         Exceptions
         Threading 
             Thread priority

Have you considered the nested set model? It might be worth it if your tree gets deeper. (How do you order siblings at the moment?) — DCoder
you should go for some server-side coding also along with Mysql. — Kalpesh
hope this may be helpful : stackoverflow.com/questions/11064913/… — Sashi Kant

Chris Trahey Chris Trahey · Accepted Answer · 2012-09-06T14:23:31

Unfortunately, MySQL does not support a variable number of JOINs, as MS SQL (and I think Postgres?) do. However, if you are willing to re-evaluate the schema, there has been some good thinking on the matter, and there are several good options in this presentation by Bill Karwin at Percona

As it stands, you have essentially two options:

Choose an arbitrary depth to query to
Select every single record and do the aggregation in your application layer (ick!)

query for selectiong parents and child

2 Answers