cookiejar
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'
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),'
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'
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
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
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also, what do you mean by deparment like '{dept_name%}'?