I am programming in PHP / MySQL / Javascript. I have a list of parts which we want to link in a child / parent relationship with no limit on the amount of tiers.
When I am picking from a list of parts to add a child to a parent I limit the list of parts to exclude the parent itself, and any parts which are already children of that parent.
What I have discovered is that I also want to exclude the grandparents of the parent as otherwise we can get an incestuous relationship, which when I display the tree of parts will create an infinite loop.
Not only that but I cannot allow the child part to be a great grandparent of the parent or great great grandparent e.t.c.
Here is the SQL statement I use currently which I think could also be improved by using LEFT JOIN but I am not skillful enough with SQL at this point.
SELECT *
FROM sch_part_general
WHERE (sch_part_general.part_id <> $parentId)
AND (sch_part_general.part_id NOT IN
(SELECT part_id FROM sch_part_mapping WHERE parent_id = $parentId)
)
sch_part_general is a multi column table with all the parts, with part_id as the primary key. sch_part_mapping is a two column mapping table with part_id (child) || parent_id (parent).
Could someone point me in the right direction with the SQL query? I am not keen on using a while loop to create the SQL statement as I think this will be quite inefficient but it is the only way I have considered might work so far.