Query in table with self-relationship

Question

Query in table with self-relationship

Navigation

#1 by (10 votes)
#2 by (3 votes)

0

I have the following table containing auto-relationship between columns category and parent:

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, otherwise the parent column will have the idcategory value of its corresponding parent category. I tried the following, but it did not work because the parent column is sometimes zero, which would force the display of only values with parent categories. Following:

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

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

mysql sql

asked by anonymous 16.12.2014 / 21:04

2 answers

3

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;

16.12.2014 / 21:10

"content: attr" does not validate in W3C How to solve a string formatting error?

score 10 · Accepted Answer

If you want all categories, I think it's the case of using LEFT JOIN:

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

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

See more about JOINs this link .