1

I need to make a inner join in a mysql table, but it's the same table.

I have a table categories: An example of this table is:

id | id_lingua | id_registro | categoria | nivel
1    1           1             Pastas      0
2    2           1             Folders     0
3    3           1             Carpetas    0
4    1           2             Feminina    1
5    2           2             Women       1
6    3           2             Mujeres     1

nivel is the key that defines whether it is a category or sub category and I need to list these categories and next to specify the parent category level across the field that will contain the id_registro the parent category.

I do not know if I was clear but how do the inner join this table?

the table is: 

id  id_lingua  id_registro  categoria  nivel
1   1          3            Pastas     0
2   2          3            Folders    0
3   3          3            Carpetas   0
4   1          4            Feminina   3
5   2          4            Women      3
6   3          4            Mujeres    3

and this consult:

SELECT cat.*, parent.categoria as nome
FROM categorias cat
INNER JOIN categorias parent
ON cat.nivel=parent.id_registro
AND cat.id_lingua=2

Return:

id  id_lingua  id_registro  categoria  nivel  nome
5   2          4            Women      3      Pastas
5   2          4            Women      3      Folders
5   2          4            Women      3      Carpetas
Ederson dos Santos
  • 105
  • 4
  • 13

1 Answers1

12

I think you want LEFT JOIN (which shows rows for which parent didn't get a match).

It also seems that you want to match on id_lingua as well.

SELECT cat.*, parent.categoria as nome
FROM categorias cat
LEFT JOIN categorias parent
ON cat.nivel = parent.id_registro
  AND cat.id_lingua = parent.id_lingua
WHERE cat.id_lingua = 2

You may also want to add IFNULL, since parent.* will be NULL for no match and it looks like you want Empty to be displayed: (unless you meant an actual empty string)

SELECT cat.*, IFNULL(parent.categoria, 'Empty') as nome
FROM categorias cat
LEFT JOIN categorias parent
ON cat.nivel = parent.id_registro
  AND cat.id_lingua = parent.id_lingua
WHERE cat.id_lingua = 2

This answer has a nice picture explaining the difference between different joins:

Image

Community
  • 1
  • 1
Bernhard Barker
  • 54,589
  • 14
  • 104
  • 138
  • Ok, so right back here with his explanation: Not only returns the categories with level = 0 `SELECT cat. * , IFNULL( parent.categoria, 'Empty' ) AS nome FROM categorias cat LEFT JOIN categorias parent ON cat.nivel = parent.id_registro WHERE cat.id_lingua = parent.id_lingua AND cat.id_lingua = '2'` – Ederson dos Santos Apr 16 '13 at 16:03
  • @EdersondosSantos `cat.id_lingua = parent.id_lingua` needs to be in the `JOIN` (like in my answer), because for level = 0, `parent.id_lingua` is `NULL`, so the check in `WHERE` will be false and it will not display those rows. – Bernhard Barker Apr 16 '13 at 16:07
  • Google translated wrong what I said before. Sorry. I meant that worked with his explanation, but does not return records with level = 0 – Ederson dos Santos Apr 16 '13 at 16:07
  • but if I put it like this::`SELECT cat. * , IFNULL( parent.categoria, 'Empty' ) AS nome FROM categorias cat LEFT JOIN categorias parent ON cat.nivel = parent.id_registro AND cat.id_lingua = parent.id_lingua AND cat.id_lingua = '2'` return all linguage – Ederson dos Santos Apr 16 '13 at 16:09
  • @EdersondosSantos `cat.id_lingua = '2'` still needs to be in the `WHERE`. – Bernhard Barker Apr 16 '13 at 16:10
  • wonderful work now. Thank you for your attention.`SELECT cat. * , IFNULL( parent.categoria, 'Empty' ) AS nome FROM categorias cat LEFT JOIN categorias parent ON cat.nivel = parent.id_registro AND cat.id_lingua = parent.id_lingua WHERE cat.id_lingua = '2'` – Ederson dos Santos Apr 16 '13 at 16:13