simplify a double query

[LoOpy]

Hi im not sure if this has been talked about because im not sure how to search for what im after. Im running MYSQL 4.1. Basicaly i have a single table with and id and parent_id to ref another row in the same table now at the moment in querying the table twice. So what im after is just 1 query to do the same job.

 

CREATE TABLE IF NOT EXISTS `database` (
  `id` tinyint(4) NOT NULL auto_increment,
  `parent_id` tinyint(4) NOT NULL,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

 

query 1

SELECT id FROM database WHERE id = 'someid'

query2

SELECT id,name FROM database WHERE parent_id = 'query1_Id'

 

hope iv explained what im after.

[aschk]

From what I can see you're looking for all the children of a particular parent.

Thus:

SELECT c.id
      ,c.name 
FROM `database` p
JOIN `database` c ON c.parent_id = p.id
WHERE p.id = 'someid'

 

p stands for parent

c stands for child

 

This is more commonly called a "self join".

[aschk]

Before you go any further though I would like to point you to http://dev.mysql.com/tech-resources/articles/hierarchical-data.html. If only I found this before I put together my first heirarchical database.

 

What you're interested in is called "The Nested Set Model".

[LoOpy]

thanks alot aschk and that link will definatly help will take some time and go through everything.