MySQL category with parent_id - SELF Join

Question

My SQL table called categories has the following structure:

CREATE TABLE categories(
   id int NOT NULL AUTO_INCREMENT,
   parent_id int,
   name varchar(50),
   PRIMARY KEY(id)
);

I would like to know if it's possible to JOIN the same table and present the parent_id name. I can do this by PHP code, but I would like, because of performance issues, to retrieve as SQL Query.

See example at SQLFiddle

I've managed to JOIN the table, but somehow the values aren't right. For example, the result of SQLFiddle retrieves:

    ID |    Name     | Parent Category Name
   -----------------------------------
    1       Meats          Steaks

Which is wrong, it should be:

    ID |    Name     | Parent Category Name
   -----------------------------------
    3       Steaks         Meats

Answer 1

Use INNER JOIN instead of LEFT JOIN and you have to join on the id equal to the parent_id of the other table like this:

SELECT     c.id, c.name, parents.name AS `Parent Category Name`
FROM       categories AS c
INNER JOIN  categories AS parents ON parents.id = c.parent_id
ORDER BY   c.name ASC;

• SQL Fiddle Demo

This will give you:

| ID |   NAME | PARENT CATEGORY NAME |
|----|--------|----------------------|
|  3 | Steaks |                Meats |

---

If you want to include those categories with no parent, use LEFT JOIN instead of INNER JOIN.

Answer 2

Try below:

SELECT     a.id, a.name,(select b.name from categories b where b.id=a.parent_id) AS `Parent Category Name`
 FROM       categories a

Answer 3

Of course you can do a self join. For the syntax to work you need to use aliases:

select c.id, c.name, cp.name as parent_category_name
from categories c left join
     categories cp
     on c.parent_id = cp.id;

In your query, you just have the join in the wrong direction. I would suggest that you use more meaningful table aliases than category and cat. In the above cp is intended to be the row for the parent.

Your on clause should be:

SELECT     category.id, category.name, cat.name AS `Parent Category Name`
FROM       categories category LEFT JOIN
           categories cat
           ON cat.id = category.parent_id
ORDER BY   category.name ASC