Generating child,root relation

Databases
1

Hi,

I’m confused generating query to show relation between two nodes.

Table structure is as mentioned below


CREATE TABLE  `category` (
  `category_id` int(11) NOT NULL auto_increment,
  `name` varchar(20) NOT NULL,
  `parent` int(11) default NULL,
  PRIMARY KEY  (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;



INSERT INTO `category` VALUES  (11,'SAMSUNG ',2),
 (2,'TELEVISIONS',1),
 (3,'TUBE',2),
 (4,'LCD',2),
 (5,'PLASMA',2),
 (6,'PORTABLE ELECTRONICS',1),
 (7,'MP3 PLAYERS',6),
 (8,'FLASH',7),
 (9,'CD PLAYERS',6),
 (10,'2 WAY RADIOS',6),
 (13,'SAMSUNG LED',12),
 (12,'SAMSUNG TV',11),
 (14,'SAMSUNG LCD',12),
 (15,'SAMSUNG LED 1',13),
 (1,'ELECTRONICS',0);

For example if we take account of category_id 13 and 1 it should return

1, 'ELECTRONICS', 0
2, 'TELEVISIONS', 1
11, 'SAMSUNG ', 2
12, 'SAMSUNG TV', 11
13, 'SAMSUNG LED', 12

i have tried something like this, but it needed to be modified


SELECT DISTINCT t1.name,t1.category_id,t1.parent FROM
`category` AS t1  JOIN `category` as t2
ON t1.category_id = t2.parent   ORDER BY t1.`parent`

How could i do this?

2

it looks like you want to create a breadcrumb trail from the root all the way down to the selected node

this is discussed here: Categories and Subcategories

3

4 or 5 levels is fine… even 15 levels is okay

4

Hi,

I have forgot to tell you about the DB contents.DB details will increase .So level of relation vary and may be more than 4 or 5(depends).