I have a table
`Categories` with fields (category_id, category, parent_category_id)
I want to write in a single query to replace the parent_category_id with the corresponding category_name.
// OBJECTIVE: To return all category names and parent category names // if category is root (no parent), parent category name is empty string SELECT x.*, y.category AS parent_category FROM AccountCategories x, AccountCategories y WHERE x.parent_category_id=y.category_id // however, the above code only returns sub categories, // root categories with a parent_category_id match is not returned
However, some parent_category_id is 0 for those categories that are top most. How can I write the query so that if parent_category_id=0, parent_category is empty string?
Thanks in advance.