Join the same table and retrieving all records

[amrigo]

Hi

Using mysql I need to retrieve from the same table, the course_categories, the id and his name but also the name of the parent course_categorie for those who have a parent categorie.

Doing a inner join does not retrieve the course_categorie who have no parent course_categorie (parent collumn set to 0)

I try this query :
SELECT cc.id AS id, CONCAT( cca.name, " / ", cc.name ) AS name
FROM course_categories cc
INNER JOIN course_categories cca ON cc.parent = cca.id

How can i list all the course_categories

Thank´s in advance

In the example below just four categories would be listed, wich query must be done to retrieve all five course_categories ?
course_categorie(id, name, parent)
1 | A | 0
2 | B | 1
3 | C | 1
4 | D | 2
5 | E | 4

[TiGGi]

Try FULL OUTER JOIN instead of INNER JOIN

[criterion9]

Or a LEFT JOIN could work there though you will still retrieve whatever the default value is as one of the parts of your CONCAT either way.