Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

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'
0
cookiejar
Asked:
cookiejar
  • 2
  • 2
1 Solution
 
lwadwellCommented:
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%}'?
0
 
cookiejarAuthor Commented:
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
0
 
cookiejarAuthor Commented:
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.
0
 
lwadwellCommented:
lwadwell,

I kind of understand.  Let me explain my concern.

SELECT Class_Title, NVL(Person_name, 'No one took this class') as person
FROM class_titles
LEFT JOIN people_classes
ON class_titles.title_id = people_classes.title_id

Should give you all classes and the people who have taken them, and "no one ... " if nobody has.

To join this back to department would be fine IF someone took the class ... you will have a dept_id, but when no-one took the class, there is nothing to join on.

So, the only possible SQL I can think of (at this moment) is:

SELECT Dept_Type, Dept_name, Class_Title, NVL(Person_name, 'No one took this class') as person
FROM class_titles
LEFT JOIN people_classes
ON class_titles.title_id = people_classes.title_id
LEFT JOIN department
ON people_classes.dept_id = department.dept_id
AND deparment like '{dept_name%}'

I am not sure if this is what you want.

lwadwell
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now