Hierarchical Data Problem

[ShoeLace1291]

I am developing an article system with PHP. I have a table for articles and a table for article categories. There are two levels of categories: plain old categories, and subcategories. Articles can have a category id of either regular categories or of a subcategory. I would like to get all articles in a category and if that category has subcategories under it, the articles from those subcategories as well. And then order the articles by their id. This is the query I have so far, but it obviously only gets articles in the parent category and not from the parent categories' subcategories.

 

SELECT
     article_id, category_id
FROM articles
WHERE category_id = 1
ORDER BY article_id DESC
LIMIT 10

CREATE TABLE IF NOT EXISTS `articles` (
  `article_id` int(15) NOT NULL AUTO_INCREMENT,
  `author_id` int(15) NOT NULL,
  `category_id` int(15) NOT NULL,
  `modification_id` int(15) NOT NULL,
  `title` varchar(125) NOT NULL,
  `content` text NOT NULL,
  `date_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` tinyint(1) NOT NULL,
  `attachment_id` int(15) NOT NULL,
  PRIMARY KEY (`article_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `article_categories` (
  `category_id` int(15) NOT NULL AUTO_INCREMENT,
  `parent_id` int(15) NOT NULL,
  `title` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `attachment_id` text NOT NULL,
  `enable_comments` tinyint(1) NOT NULL,
  `enable_ratings` tinyint(1) NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Thanks in advance.

[requinix]

It's not outright stated so I want to confirm it: subcategories can themselves have subcategories, right?

[kicken]

Have a look at the Nested Set Model for storing your data rather than parent/child relationships. It's easier to work with when your looking to query across hierarchy levels.