MySQL category with parent_id – SELF Join

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

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.