SQL Query with Sub Select

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
where
      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
ASSIGNMENTS.docx
cbridgmanAsked:
Who is Participating?
 
Jan LouwerensSoftware EngineerCommented:
Using a correlated subquery would look something like this:
select WONUM, PARENTWONUM, (SELECT ASSIGNEE FROM ASSIGNMENT where WONUM = WORKORDER.WONUM) AS ASSIGNEE from WORKORDER
order by WONUM, PARENTWONUM, ASSIGNEE

Open in new window


However, I don't think this will work in your case, because of the 1 to many relationship of the data. If more than one row is returned in the subquery, it will fail with an error.
0
 
Jan LouwerensSoftware EngineerCommented:
From the RESULT SET in the attached document, it looks like what you're looking for is:
select WORKORDER.WONUM, WORKORDER.PARENTWONUM, ASSIGNMENT.ASSIGNEE from WORKORDER, ASSIGNMENT where
   WORKORDER.WONUM = ASSIGNMENT.WONUM
order by WORKORDER.WONUM, WORKORDER.PARENTWONUM, ASSIGNMENT.ASSIGNEE

Open in new window


However, I do notice that you state that it must be done via subselects, and not a join. Why does this constraint exist?
0
 
cbridgmanAuthor Commented:
Thanks very much Jan. That worked exactly the way I needed it to. The reason for the sub-select constraint is that I'm running this within some application software that doesn't understand joins. The software only takes a "where" clause so you have to embed subselects and not joins in it.
0
 
cbridgmanAuthor Commented:
Thanks Jan, we came up with a method inside the software to get this done.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.