Consultation in table with self-relationship

Question

I have the following table containing self-relationship between the idcategoria and parent columns:

idcategoria categoria   pai
1           Camisetas   0
2           Regata      1

When an added category does not have a parent category the column value will be zero, and otherwise the parent column will have the idcategory value of its corresponding parent category. I tried it as follows, but it didn’t work because the parent column was sometimes zero, which would force the display of only values with parent categories. Follows:

SELECT c.*, p.categoria AS catpai FROM categoria AS c, categoria AS p WHERE p.pai = c.idcategoria;

The intention is to take as a result all the categories and the name of the PAI categories that have parent category. I will only work with two levels.

Answer 1

If you want all categories, it seems to me the case to use LEFT JOIN:

SELECT
   c.*,
   p.categoria AS catpai
FROM
   categoria AS c
LEFT JOIN
   categoria AS p ON c.pai = p.idcategoria;

Notice that I used c.pai = p.categoria, and not the reverse as in its original.

See more about Joins at this link.

Answer 2

Look if this is what you need:

SELECT 
  c.*, p.categoria AS catpai 
FROM 
  categoria AS c
  left join categoria AS p on p.pai = c.idcategoria;