SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Generating child,root relation

    Hi,

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

    Table structure is as mentioned below

    Code:
    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;

    Code:
    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

    Code:
    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

    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    4 or 5 levels is fine... even 15 levels is okay
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •