Display Non-Parent items using a SQL script
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)
ShowChild.nNON.Parents.fromPO.pdf
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)
ShowChild.nNON.Parents.fromPO.pdf
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
why do you use "on POH_id = 1" ?
I assumed that you wanted the report for one particular PO. So there is "on POH_id = 1". If it is so then you have to pass this as parameter.
ASKER
YES! it worked excellently.
One las thing: can you explaine the lines of:
Case
inner join
left outer join
Thanx
One las thing: can you explaine the lines of:
Case
inner join
left outer join
Thanx
inner join
inv inv1
on inv1.INV_id = POD_itemId -- join between po detail and inventory. On item id.
left outer join
inv inv2
on inv2.INV_rel = inv1.INV_id -- left outer join done to fetch all child items.
Why left outer join?
1. Reason is that not all items has parent child relation. Only some has.
2. Left outer join will show all the records of LEFT table even if there are no matching records. These are the items with out parent child relationship.
3. Left outer join will show all matcing records. Notice the ON clause of left outer join. The join is between po item and inventory relation ship. So if po has one item then all its child will appear due to this join.
Why case?
1. As explained above left outer join will show data even if there is no matching. In that case I have to show the data from parent. If there are matching then I show the child.
I tried to explain... But it is hard. The code is self explainable.
inv inv1
on inv1.INV_id = POD_itemId -- join between po detail and inventory. On item id.
left outer join
inv inv2
on inv2.INV_rel = inv1.INV_id -- left outer join done to fetch all child items.
Why left outer join?
1. Reason is that not all items has parent child relation. Only some has.
2. Left outer join will show all the records of LEFT table even if there are no matching records. These are the items with out parent child relationship.
3. Left outer join will show all matcing records. Notice the ON clause of left outer join. The join is between po item and inventory relation ship. So if po has one item then all its child will appear due to this join.
Why case?
1. As explained above left outer join will show data even if there is no matching. In that case I have to show the data from parent. If there are matching then I show the child.
I tried to explain... But it is hard. The code is self explainable.
ASKER
hey, you explained enough ... Thanx!
ASKER