Help on joins within the same table

[playaz]

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

[fenway]

No need to use concat...

[code]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[/code]
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.