We have an inventory table where in the same table there is a relation of Parent/Child. For exampe a Parent is a CASE of COOR'S and the Child would be the CAN or 6PACK of COOR's.
We've been working on a query where we want to print the items a purchase order. In the PO, we order Parent item mixed with non-Parent (those are Items that are not Child or Parent).
What we need is to print the PO with all its items, but when there's a Parent Item in the PO, we want to print all thier Child, not the Parent (see attachment).
In the attachment we place the 3 tables: Po Header, Po Line Items and Item. Also we included the display result of the Po Report " QUERY RESULT" (the circles are the relation or pointers within the tables)
EE has assisted us in a script where we can do just that but within the Item tables only (see below). What we need is to embed the same result but in the PO Report.
Script that prints Non-Parents and Child from Item table:
select * from item a
where not exists(select 1 from item where a.id = childparentrelate)