TROUBLE IN MULTIPLE SUBSELECT QUERY

Question

I need to find the name, function, officename and the salary of all employee with the same function as PETER or a salary greater or equal than the salary of SANDERS. order by function and salary.

There are two tables: office and employee

table office contains:

officenumber

name

city

table employee contains:

employeenumber
name
function
manager
sal
officenumber

this is my current SQL query:

SELECT  NAME,
        FUNCTION,
        SAL
FROM    EMPLOYEE
WHERE   FUNCTIE   =   (SELECT     FUNCTION
                       FROM       EMPLOYEE
                       WHERE      NAME = 'PIETERS')

I'm stuck with the query.

hello PM77-1, how does the next query look like? to create subselect for 'SANDERS' ? — Regazzi, Dec 22 '16 at 20:45

Jim D · Answer 1 · 2016-12-22T20:51:29.840

Assuming this is SQL Server (you never specified), something like this should work.

SELECT
    e.name,
    e.function,
    e.sal,
    o.name AS officename
FROM employee e 
    JOIN office o ON e.officenumber = o.officenumber
WHERE
    e.function = (SELECT function FROM employee WHERE name = 'PIETERS') OR
    e.sal >= (SELECT sal FROM employee WHERE name = 'SANDERS')
ORDER BY e.function, e.salary

You'll have to tweak this a bit if you're working with MySQL or something else.

score 1 · Answer 2 · edited Dec 22 '16 at 22:41

Three things you need to do here:
1. join the two tables, since you need results from both tables
2. filter the results according to the two criterias
3. order the results:

The first part is easy, just need to join them according to the officenumber:

select e.name, e.function, o.name as officeName, e.salary from
  employee e inner join office o 
  on e.officenumber = o.officenumber

second part, simple where clause:

  where e.function = (select function from employee where name = 'PETER') 
  or e.salary >= (select salary from employee where name = 'SANDERS')

and the last, ordering:

  order by e.function, e.salary

Putting it all together:

select e.name, e.function, o.name as officeName, e.salary from
  employee e inner join office o 
  on e.officenumber = o.officenumber
  where e.function = (select function from employee where name = 'PETER') 
  or e.salary >= (select salary from employee where name = 'SANDERS')
  order by e.function, e.salary

Nir - I will edit your post to fix a typo (salery) and to remove "as" from table aliases. In Oracle you are not allowed to use "as" between a table name and its alias. You may use "as" between a column name (or an expression to be used as a column) and a column alias, and it's a good practice but it is never required; but for table aliases, it's simply not permitted. I see that the DB product wasn't known when you wrote this, but now it is, and it's Oracle - I am editing your post for future visitors to this thread. — , Dec 22 '16 at 22:40

TROUBLE IN MULTIPLE SUBSELECT QUERY

2 Answers2