Link to home
Start Free TrialLog in
Avatar of cookiejar
cookiejarFlag for United States of America

asked on

PL/SQL THREE LEVEL SUBQUERY

I am a novice in creating sub queries but I would like to do something like the following:

select dept_type,dept_name
   select class_title
        NVL((select person_name
                from people_classes
                   where people_classes.dept_id = department.dept_id
                    and class_titles.title_id = people_classes.title_id),'No one took this class  in this department')
      from class_titles          
from department where deparment like '{dept_name%}'

I would like to return the following result
Dept_Type, Dept_name, Class_Title, Person_name or
Dept_Type, Dept_name, Cass_Title, 'No one took this class'
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

What you are asking for is a little bit confusing.  For it to work (and you probably should use LEFT JOINs not sub queries) you need a relationship between department and class_titles - you have not shown one in your pseudo-query.

Also, what do you mean by deparment like '{dept_name%}'?
Avatar of cookiejar

ASKER

The '{?dept_name}%' is the prompt parameter. In our organization, we have workcenters in one department. For example return every thing that begins with Dept3, will bring back Dept31, Dept32, Dept33, etc.

My objective is check to see if anyone in the entered department has taken the course. If no one has taken the course in that department, return the department, course name, and the string, 'no one has taken this course in department A.'  If someone has taken a course in that dept, return, the department, course, person.


For example:
Dept.   Course   Person

A       Math     Johnson, K.
A       Science  Smith, T.
B       English  Pearson, J.
C       English  No on has taken this course
Oh yes, the relationship exists between the person_training_record and the department, which is a department_id.  Also a relationship exists between the person_training_record and the required_courses table, which is the course_id.
ASKER CERTIFIED SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial