getting child category's parent name with a single query in mysql
mysql
Solution
Join the table with itself, using the `parent` column to link to the cat_id of the parent.
SELECT c1.cat_id as childID, c1.cat_name ChildName, c2.cat_name as ParentName
from category c1
LEFT OUTER JOIN category c2
ON c1.parent = c2.cat_id
- Be careful: since some elements have no parents (`NULL`), I put a `LEFT OUTER JOIN` so those rows are displayed as well. If you don't want that, use a `JOIN` instead of `LEFT OUTER JOIN`.
- You can also show the lines, but display something else (empty or a text or ...) instead of the `NULL` by using `COALESCE`.
- You can consider the result as one (big) new table, so you can add WHERE clauses as you usually do, for example filtering on the parent name: `WHERE c2.cat_name = 'test2'`
Problem
I have a mysql table named "category". the basic structure looks like this- ``` ------ -------- ------ cat_id cat_name parent ------ -------- ------ 1 test1 NULL 2 test2 NULL 3 test3 2 4 test4 1 5 test5 3 ``` now i want all the category data with parent category name (not only id) in a single query. is that possible? i could do it by using a second query (getting child's parent name) in a while loop and merging data as a whole. but is it possible to do this with a single query?