To expand on RabidDog's idea using a recursive solution, I am in agreement with that.
The first solution I provided was just the SQL solution, which can be taken in a global applicative solution, depending on what he needs as an approach vertical/horizontal/both.
SELECT c.ID_CATEGORY, c.NAME CATEGORY, ID_SUB_CATEGORY, c2.NAME_CATEGORY FROM CATEGORY c JOIN RELATION r ON (c.ID_CATEGORY=r.ID_CATEGORY) JOIN CATEGORY c2 ON(ID_SUB_CATEGORY=c2.ID_CATEGORY) ORDER BY c.ID_CATEGORY
Vertical, something looking like that:
function single_genealogy($category, $level = 0)
$q = "SELECT ID_SUB_CATERORY, NAME_CATEGORY FROM RELATION JOIN CATEGORY c USING(ID_CATEGORY) WHERE c.ID_CATEGORY=".$category;
$r = mysql_query($q); //or die/mail/echo mysql_error()
while($d = mysql_fetch_row($q))
//displaying current node : $d...
//recursive call :
for a given category, that'll show :
Very importantly, a category mustn't be twice in a single genealogical tree otherwise it'll turn the recursive solution into a perpetual one! 🙂