Jump to content
Updating IPB tonight Read more... ×


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


Help on joins within the same table

Recommended Posts

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

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

Share this post

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

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.