Hi
Am using MySQL. If it was Oracle I could have used CONNECT BY PRIOR to achieve this. Am completely stumped by this one! My original query seems almost there but it doesnt list any of the top level locations on their own. What am I missing???
Code:
SELECT t1.location_name AS lev1,
t2.location_name as lev2,
t3.location_name as lev3
FROM tbl_locations AS t1
LEFT JOIN tbl_locations AS t2 ON t2.location_parent_id = t1.location_id
LEFT JOIN tbl_locations AS t3 ON t3.location_parent_id = t2.location_id
WHERE t1.location_parent_id = 0 order by t1.Location_Name,
t2.Location_Name,
t3.Location_Name ASC;