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](http://www.fliquidstudios.com/2008/12/23/nested-set-in-mysql/)? It might be worth it if your tree gets deeper. (How do you order siblings at the moment?) — DCoder, Sep 06 '12 at 14:23
you should go for some server-side coding also along with Mysql. — Kalpesh, Sep 06 '12 at 14:27
hope this may be helpful : http://stackoverflow.com/questions/11064913/achieve-hierarchy-parent-child-relationship-in-an-effective-and-easy-way — Sashi Kant, Jul 01 '13 at 08:17

Chris Trahey · Answer 1 · 2012-09-06T14:29:38.253

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!)

score 0 · Answer 2 · answered Sep 06 '12 at 14:18

0

In MS SQL I think you'd need to use Recursive Queries Using Common Table Expressions. So perhaps that might help find what you're looking for in the MySQL space.

answered Sep 06 '12 at 14:18

Sepster

4,800
20
38

query for selectiong parents and child

2 Answers2