I have this hierarchical table that will end up containing 5 or 600 unique names, many of which will be assigned to multiple parents, such as "Hunt" below...
+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | Phillips | NULL |
| 2 | Barnes | 1 |
| 3 | Moore | 2 |
| 4 | de Mohr | 3 |
| 5 | Oswald | 9 |
| 6 | Hunt | 2 |
| 7 | Burnham | 6 |
| 8 | Buckley | 7 |
| 9 | Paine | 4 |
| 10 | Sturgis | 6 |
| 11 | Hunt | 3 |
+-------------+----------------------+--------+
Query:
SELECT
t1.name AS lev1,
t2.name AS lev2,
t3.name AS lev3,
t4.name AS lev4,
t5.name AS lev5,
t6.name AS lev6
FROM category t1
LEFT JOIN category t2
ON t2.parent = t1.category_id
LEFT JOIN category t3
ON t3.parent = t2.category_id
LEFT JOIN category t4
ON t4.parent = t3.category_id
LEFT JOIN category t5
ON t5.parent = t4.category_id
LEFT JOIN category t6
ON t6.parent = t5.category_id
WHERE t1.name = 'Phillips'
Output:
+-------------+-------------+-------------+---------+---------+---------+
| lev1 | lev2 | lev3 | lev4 | lev5 | lev6 |
+-------------+-------------+-------------+---------+---------+---------+
| Phillips | Barnes | Hunt | Sturgis | NULL | NULL |
| Phillips | Barnes | Moore | de Mohr | Paine | Oswald |
| Phillips | Barnes | Hunt | Burnham | Buckley | NULL |
+-------------+-------------+-------------+---------+---------+---------+
I just have 2 questions about this.
Is it going to be wiser to incorporate a linking table to avoid duplicating "names" for each parent in one table, or is there some other way to avoid this, or is this even an issue?
The output I'll be getting for each page will be a long, fairly complex outline layout, which would probably mean that I'd need to run this kind of query sometimes up to 30 or 40 times per page. Is this kind of query something I can run that many times to load a page without a problem, or do I need to come up with something more efficient?
