Summarizing Information › Summarizing Information From Another Table

Summarizing Information From Another Table

Using a nested SELECT statement

A SELECT statement within a WHERE clause is called a nested SELECT. You can use a nested SELECT statement to retrieve information from one table based on summary information in another table.

Example— COUNT in a nested SELECT

List the jobs held by more than one employee. The SELECT statement contains a nested SELECT that returns the number of employees assigned to each job ID. The primary SELECT statement determines if the number is greater than 1:

select distinct jobid, title
  from joblist
  where 1 <
    (select count(*)
     from emp
     where joblist.jobid = emp.jobid) ! display

                                 JOBLIST REPORT
                                    02/10/99

                  JOBID                         TITLE

                    3001                 MGR INTERNL SOFTWARE
                    3025                 PROGRAMMER/ANALYST
                    4025                 PR WRITER
     END OF REPORT