嵌套集模型MySQL

Question

I have to two tables category and category_description. With columns

`category` (
  `category_id`,
  `image` ,
  `parent_id` ,
  `sort_order`,
  `status` ,
  `created`,
  `modified`,
  PRIMARY KEY (`category_id`)
)

`category_description` (
  `category_id`,
  `name` ,
  `description`,
  `meta_description`,
  `seo_keyword`,
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `name` (`name`)
)

After insert into category table get the last category_id and insert it to category_description table. My Question is how i can select category with parnet and child name and display like this

if category has a child it should display this " parent_name > child_name " if not should display this " parent_name "

Answer 1

Run this (it assumes the parent categories have a NULL in their parent_id column):

SELECT CONCAT(parent_description.name, 
              COALESCE(CONCAT(' > ', child_description.name)
                       ,'')
              ) AS display_this
FROM category AS parent
JOIN category_description AS parent_description
  ON parent.category_id = parent_description.category_id
LEFT JOIN category AS child
  ON parent.category_id = child.parent_id
LEFT JOIN category_description AS child_description
  ON child.category_id = child_description.category_id
WHERE parent.parent_id IS NULL