Posted on 2009-04-30
Last Modified: 2013-12-07
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'
Question by:cookiejar
    LVL 25

    Expert Comment

    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%}'?

    Author Comment

    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

    Author Comment

    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.
    LVL 25

    Accepted Solution


    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.


    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now