hi r937. Apologies, yes you're right the column in red should be items.category_id
I edited the query slightly shortening some category names etc for clarity and accidentally erased that.
second, why isn't the ORDER BY column in the SELECT clause? i'd be surprised if you actually got the sorting to work correctly
Ah I hadn't realised that and you are right the sorting isn't in fact working it was just a lucky coincidence they were sorted in the right order probably due to the order the data was inserted.
That query is actually an attempt by somebody else after I presented my initial attempt of this monstrosity to them...
LEFT JOIN millnedev.ips_classifieds_items
ON (ips_classifieds_categories.category_id = ips_classifieds_items.category_id)
LEFT JOIN millnedev.ips_classifieds_categories AS ips_classifieds_categories_1
ON (ips_classifieds_items.category_id = ips_classifieds_categories_1.category_id)
WHERE (ips_classifieds_categories_1.lft >= ips_classifieds_categories.lft
AND ips_classifieds_categories_1.rgt <= ips_classifieds_categories.rgt)
GROUP BY ips_classifieds_categories.category_id
ORDER BY ips_classifieds_categories.lft ASC;
I've added the ORDER BY column into the SELECT clause and the sorting now appears to be working.