Jump to content


Photo

Joins within the same table


  • Please log in to reply
1 reply to this topic

#1 playaz

playaz
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 28 September 2006 - 01:40 PM

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.images...yexampleqp8.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

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 September 2006 - 02:55 PM

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.
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

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users