same table Order by help

[kevisazombie]

Hi all,

 

I am still pretty green when it comes to MYSQL. I have a categories table that looks like this:

CREATE TABLE IF NOT EXISTS `categories` (
  `category_id` int(10) unsigned NOT NULL auto_increment,
  `category_name` varchar(45) NOT NULL,
  `parent_id` int(10) unsigned default NULL,
  `category_type_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`category_id`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_name` (`category_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=217 ;

 

We store Parent categories in the same table as the child categories. parent categories have a parent_id = 0. What I want to do is just pull all the categories and order them by parent_id but also have the parent categories grouped with the children.

 

so I need something like  'SELECT * FROM categories ORDER BY parent_id"

 

but taken further so that the parents are not all grouped together. I do not know if I need a subquery or what.

 

Thanks in advance.

[sdi126]

couldn't you just group them first by parent and then category...

 

maybe I don't quite understand but it sounds like you want to do:

 

SELECT * FROM categories ORDER BY parent_id,category_id

[kevisazombie]

To better explain my issue. Using what sdi126 recommend and what I have tried I am getting table rows returned in an order similar to:

 

parent1

parent2

parent3

parent4

child of parent 1

child of parent 1

child of parent 1

child of parent 1

child of parent 2

child of parent 2

child of parent 2

etc..

 

I am trying to get them returned like this:

 

Parent1

child of parent1

child of parent1

Parent2

child of parent2

child of parent2

Parent3

child of parent 3

child of parent 3

etc...

[fenway]

That's going to be impossible... why not just JOIN them to have the relationship captured in each row?