Jump to content


Photo

Help on 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 20 April 2006 - 11:06 AM

Hi guys,

I am having problems with self joins - I've used inner joins before on seperate tables but never when only one is used. I have a category table which looks as follows:

Below is an example of the database
[a href=\"http://imageshack.us\" target=\"_blank\"][img src=\"http://img208.imageshack.us/img208/1307/category2jd.jpg\" border=\"0\" alt=\"IPB Image\" /][/a]

I want to use a join and CONCAT to make it simplier to understand which category a sub-category belongs to..
eg

Car & Motorbike are BOTH level 1 (main category)

While 4x4, Rally and Premium are all level 2 (sub-categories) - these are sub categories of the Car category
as defined by the parent_id field so for instance using the CONCAT i want to somehow get it looking like this..

Car -> 4x4
Car -> Rally
Car -> Premium

Can anyone help me out im pulling my hair out :)

Thanks in advance

#2 fenway

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

Posted 21 April 2006 - 04:33 PM

No need to use concat...

SELECT parent.category AS category, sub.category AS subcategory
FROM categories AS parent
JOIN categories AS sub ON ( parent.category_id = sub.parent_id )
WHERE parent.category_id <> sub.category_id
ORDER BY parent.category_id, sub.category_id
The WHERE clause simply prevents the parent categories from showing up in the result set, and you can ORDER BY whatever you want (i.e. the name itself).

Hope that helps.
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