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