Joins within the same table

[playaz]

Can anyone explain how I perform joins within a single table - I know how to do it with 2 tables but get confused when having to do it a single table.

See the image below
http://img220.imageshack.us/my.php?image=categoryexampleqp8.jpg

I want to display items with the CONCAT command to include the 'parent' category before the 'child' category.

eg. so it displays like this:
IT -> Desktop Technician
Legal -> Solictor
Medical -> Nurse

Can anyone explain how I achieve this with joins and the concat command.

Thanks in advance :)


Thanks in advance

[fenway]

You simply need to use table aliases to uniquely identify each "instance" of this table... I'm assuming you don't want the main categories, and your table is indexed properly.
[code]
SELECT CONCAT( p.category, ' -> ', c.category )
FROM yourTable AS c
INNER JOIN yourTable AS p ON ( p.category_id = c.parent_id )
WHERE c.parent_id > 0
[/code]