Jump to content

Archived

This topic is now archived and is closed to further replies.

playaz

Joins within the same table

Recommended Posts

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.