Hi all,

I was hoping that someone can help me with a complex query I'm trying to write.

I have 2 tables i) category & ii) article. The category table has 3 fields (category_id, name & parent_id) & the article table has 4 fields (article_id, title, body & category_id).

The category table has the following test data in it:

Code:
cat_id - Name - parent_id
1 - Home - 0 
2 - Products - 0 
3 - About - 0 
4 - TestItem - 2
The parent_id relates to the cat_id, I use it to build up sections in a webpage.

The article table has the following test data in it:

Code:
article_id - title - body - cat_id
1 - Test title 1 - some body data - 1 
2 - Test title 2 - some more data - 3 
3 - Test title 3 - even more data - 5
The cat_id in this table relates to the cat_id in the category table.

The query I have so far is as follows:
Code:
SELECT c1.title, c2.name as node1, c3.name as node2 
FROM core_content c1 
LEFT JOIN core_category AS c2 ON c2.cat_id = c1.cat_id 
LEFT JOIN core_category AS c3 ON c3.cat_id = c2.parent_id;
The following data is returned from the query:

Code:
title - node1 - node2
Test title 1 - Home - NULL 
Test title 2 - About - NULL 
Test title 3 - TestItem - Products
I need the node entries back in the following order because TestItem is a sub category of Products:

Code:
title - node1 - node2
Test title 1 - Home - NULL 
Test title 2 - About - NULL 
Test title 3 - Products - TestItem
If anyone can help me I'd be very grateful. Thanks in advance,

Xander