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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cbridgmanAuthor Commented:
Thanks Jan, we came up with a method inside the software to get this done.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.