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.

Answer 1

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.

Answer 2

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