Quote
You could also create a view that lists all the parts of a given assembly including the root part (you might try using a union in there). You could then query the view with a part id and pull back the part and all of its associated sub-parts.
that is exactly what I need this function for. I need to list the associated parts from a given "parent" part.
I have actually renamed a couple of the fields prior to my post before about this, and I took out the category table all together, as every part is going to be in the parts category.
I do not know that much about the union function ? but I am guessing that it is much like a while loop from what I have seen so far?
the other last thing I really didn't mention in here before and something that I do not believe will have too much of an impact beyond the first level is the printer type. I replaced the "category" table with a printer table, being as that would be a much better reference.
Parts
-- part_id
-- serial
-- description
-- part_status (enum 1 or 0 - coinciding with being available and obsolete)
Part_has_child
-- parent_id
-- child_id
-- quantity
Part_has_printer
-- printer_id
-- part_id
can you give me some advice on how I can accomplish getting a list of all the "sub-parts" from a given "parent-part"
SELECT Parts.serial, Parts.description, Part_has_child.quantity
FROM Parts, Parts_has_child
WHERE Parts.part_status = 1
AND (SELECT child_id FROM Parts_has_child JOIN Parts.part_id ON Parts_has_child.parent_id)
AND (SELECT part_id FROM Parts_has_printer JOIN Parts.part_id ON Parts_has_child.printer_id)
the 2 AND clauses are trying to make sure that the child_id belongs to the parent_id and that the child_id belongs to the printer being looked at.