I've got two related Oracle tables: (1) WORKORDER and (2) ASSIGNMENT. The WORKORDER table has two attributes: (1) WONUM and (2) PARENTWONUM. If the PARENTWONUM has a value in it, that means that the record is a child of another WORKORDER. So, in that case, WONUM is the child and PARENTWONUM references the parent WORKORDER. One WORKORDER can have many children. The attached word document does a better job of illustrating this.
The ASSIGNMENT table has two attributes: (1) WONUM and (2) ASSIGNEE. The relationship between WORKORDER and ASSIGNEE is one to many (there can be 0, 1, or many ASSIGNMENT records for a single WORKORDER). Again the attached document does a better job of illustrating this.
I need to construct a query that returns who is assigned to which workorder. The attached document shows a sample result set.
The "rub" here is that this has to be done via sub selects instead of joins. I'm able to do the SQL for Work Orders and their children but adding in the ASSIGNMENT table is a bit to complex for me. For example:
select wonum, parent from workorder
wonum = '45296' or wonum in (select wonum from workorder wo2 where (wo2.wonum = '45296' or wo2.parent = '45296'))
That would get me work order 45296 and each of its children but I'm not sure how to get a subselect in there for the ASSIGNMENT table.
Let me know if you can help me.
Thanks in advance