Querying two tables... in MySQL

Question

CREATE TABLE IF NOT EXISTS `document` 
(
  `intId` int(11) NOT NULL auto_increment,
  `chDocumentTitle` varchar(32) default NULL,
  `dtLastUpdate` datetime default NULL,
  `chUser` varchar(32) default NULL,
  `chLink` varchar(256) default NULL,
  `Keyword` varchar(256) default NULL,
  `intParentid` int(11) NOT NULL,

  PRIMARY KEY  (`intId`),
  KEY `dtLastUpdate` (`dtLastUpdate`,`chUser`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

CREATE TABLE IF NOT EXISTS `category` 
(
  `intId` int(11) NOT NULL auto_increment,
  `chName` varchar(32) NOT NULL,
  `Isactive` tinyint(1) NOT NULL default '0',
  `chnestUnder` int(5) NOT NULL default '0',
  PRIMARY KEY  (`intId`),
  KEY `chName` (`chName`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

Now I am looking for a query which will do the following...

Want to list out the documents of the categories... in hierarchical order.

Category One

Documents of Category One

Sub Category - [ If any ]

Documents of Sub Category

Based on this I need to generate XML.

Answer 1

This page has a very good explanation and plenty of helpful examples on how to work with hierarchical data in MySQL. In your situation it's definitely worth the read:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

...

Also make sure to follow the link to There's also a reference to this page, with tips on how to work with hierarchical data in your database with a bit of help from PHP.