Check this out, sorry for external link but it looks ugly here (maby iam to stupid to format it correct) ^^
Join my irc channel if you like to talk to me
tinyurl.com/ylsf7ef (webchat)
A:
Use joins.
I removed some where conditions for more clarity
SELECT
c.id as c_id,
c.alias as c_alias,
c.title as c_title,
sc.id as sc_id,
sc.alias as sc_alias,
sc.title as sc_title,
a.alias as a_alias,
a.title as a_title,
sa.alias as sa_alias,
sa.title as sa_title
FROM
categories c
LEFT JOIN
articles a
ON
c.id = a.category_id
LEFT JOIN
categories sc
ON
c.id = sc.parent_id
LEFT JOIN
articles sa
ON
sc.id = sa.category_id
ORDER BY
c.rank,
a.rank,
sc.rank,
sa.rank
EDIT: As far as I can see, this is not the case here, but if you want to retrieve some unlimited depth elements, you might want to look at a way to store strong hierarchical data in a relational database.
Benoit Vidis
2010-02-18 16:43:14
i checked the link but excluding the childs to another function that also does a query does not reduce the queries at all^^
Henry
2010-02-18 17:02:38
It may or not, but the given query does. In your case, as the depth is fixed to one sub level only, the benefit is only in removing one join. In case you would need to retrieve an unknown depth, it might help if you cannot load all your nodes at once.
Benoit Vidis
2010-02-18 18:23:57