How do I inner join them?
Instead of showing the numbers under ParentID, how do I show the Name.
Figured it out. Thank you so much!
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, t7.name as lev7
FROM `criteria` AS t1
LEFT JOIN criteria AS t2 ON t2.`parent_id` = t1.`criteria_id`
LEFT JOIN criteria AS t3 ON t3.`parent_id` = t2.`criteria_id`
LEFT JOIN criteria AS t4 ON t4.`parent_id` = t3.`criteria_id`
LEFT JOIN criteria AS t5 ON t5.`parent_id` = t4.`criteria_id`
LEFT JOIN criteria AS t6 ON t6.`parent_id` = t5.`criteria_id`
LEFT JOIN criteria AS t7 ON t7.`parent_id` = t6.`criteria_id`
The best solution for this, would be to use @hericles's database schema. Then to use what's called recusrion to then spit this out recursvily with nested ul's and li's. The SQL query that @hxinen posted is anything but optimzed or dynamic. Just grab a list, then loop through the records, and nest them. Then loop that nested array until array['children'] is empty, where you can spit out a closing ul. If you want code for this just ask, and i'll post a working exapmple for you.