Sql commands using joins for the following four tables

There are four tables开发者_开发知识库

1. Employee (Employee_id,name,chief_id)
2. Department(Department_Id,name)
3. Emp_Dep(Employee_id,Department_Id)
4. Emp_Sal(Employee_id,salary)

Now I need to write a query which displays Employee IDs who get maximum salary in each department.

---

You don't say which flavour of database you are using (unless you intend SQL = SQL-Server). So this solution pay not work for you. Any, it uses Oracle's analytic function RANK() to solve the problem.

Given this data ...

SQL> select d.name
  2         , e.name
  3         , es.salary
  4  from employee e join
  5       emp_sal es on (es.employee_id = e.employee_id) join
  6       emp_dep ed on (ed.employee_id = e.employee_id) join
  7       department d on (d.department_id = ed.department_id)
  8  order by d.department_id, es.salary desc
  9  /

NAME       NAME           SALARY
---------- ---------- ----------
PRODUCTION GRINCH           1100
PRODUCTION CAT              1100
DELIVERY   LORAX            3000
DELIVERY   DAISY            2500
ACCOUNTS   FOX              5000
ACCOUNTS   KNOX             2500
ACCOUNTS   SAM-I-AM         1200

7 rows selected.

SQL>

Notice the tie in PRODUCTION, which is why this returns four rows ...

SQL> select dept_name
  2         , emp_name
  3         , salary
  4  from (
  5          select d.name as dept_name
  6                 , e.name as emp_name
  7                 , es.salary
  8                 , rank () over (partition by d.department_id
  9                                    order by es.salary desc ) as rnk
 10          from employee e join
 11               emp_sal es on (es.employee_id = e.employee_id) join
 12               emp_dep ed on (ed.employee_id = e.employee_id) join
 13               department d on (d.department_id = ed.department_id)
 14         )
 15  where rnk = 1
 16  order by salary desc
 17  /


DEPT_NAME  EMP_NAME       SALARY
---------- ---------- ----------
ACCOUNTS   FOX              5000
DELIVERY   LORAX            3000
PRODUCTION GRINCH           1100
PRODUCTION CAT              1100

SQL>

edit

I used employee names rather than IDs because it makes the results easier to read. It is easy enough for you to substitute your desired projectors.

---

This will give you the value of maximum salary:

SELECT 
  department_id, MAX(es.salary) AS max_salary
FROM 
  emp_sal es JOIN emp_dep ed ON ed.employee_id = es.employee_id
GROUP BY ed.department_id

This will give you the ID:

SELECT department_id, employee_id, max_values.max_salary
FROM 
  emp_sal es JOIN emp_dep ed ON ed.employee_id = es.employee_id
  JOIN 
    (SELECT 
       department_id, MAX(es.salary) AS max_salary
     FROM 
       emp_sal es JOIN emp_dep ed ON ed.employee_id = es.employee_id
     GROUP BY ed.department_id) AS max_values 
  ON (ed.department_id = max_values.department_id AND es.salary = max_salary)

---

Creating my own test tables:

DECLARE @Employee TABLE (Employee_id INT, Name VARCHAR(MAX), Chief_id INT)
DECLARE @Department TABLE (Department_Id INT, Name VARCHAR(MAX))
DECLARE @Emp_Dep TABLE (Employee_id INT, Department_Id INT)
DECLARE @Emp_Sal TABLE (Employee_id INT, Salary DECIMAL)

INSERT INTO @Employee
SELECT  1, 'John Doe', 0 UNION ALL
SELECT  2, 'John Doe', 0 UNION ALL
SELECT  3, 'John Doe', 0 UNION ALL
SELECT  4, 'John Doe', 0 UNION ALL
SELECT  5, 'John Doe', 0 UNION ALL
SELECT  6, 'John Doe', 0 UNION ALL
SELECT  7, 'John Doe', 0;

INSERT INTO @Department
SELECT 1, 'Moomin' UNION ALL
SELECT 2, 'Moo' UNION ALL
SELECT 3, 'Min';

INSERT INTO @Emp_Dep
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 4, 2 UNION ALL
SELECT 5, 3 UNION ALL
SELECT 6, 3 UNION ALL
SELECT 7, 3;

INSERT INTO @Emp_Sal
SELECT 1, 1200 UNION ALL
SELECT 2, 1100 UNION ALL
SELECT 3, 2000 UNION ALL
SELECT 4, 2000 UNION ALL
SELECT 5, 3451 UNION ALL
SELECT 6, 3321 UNION ALL
SELECT 7, 3123;

This version will all people in a department if they are tied :)

WITH [Merged] AS (
    SELECT [@Employee].[Employee_id],
           [@Employee].[Name],
           [@Department].[Name] [Department],
           [@Department].[Department_Id],
           [@Emp_Sal].[Salary]
      FROM @Employee
     INNER JOIN @Emp_Sal ON [@Employee].[Employee_id] = [@Emp_Sal].[Employee_id]
     INNER JOIN @Emp_Dep ON [@Emp_Sal].[Employee_id] = [@Emp_Dep].[Employee_id]
     INNER JOIN @Department ON [@Emp_Dep].[Department_Id] = [@Department].[Department_Id]
)
SELECT [Main].Employee_id, [Main].Name, [Main].Department, [Main].Salary
FROM Merged [Main]
WHERE [Salary] = (SELECT MAX(Salary) FROM [Merged] WHERE Department_Id = [Main].Department_Id);

Whereas this version will only select 1 from each department, you'll have to figure out a way to get the person you want on top in the ROW_NUMBER() OVER

WITH [Merged] AS (
    SELECT [@Employee].[Employee_id],
           [@Employee].[Name],
           [@Department].[Name] [Department],
           [@Department].[Department_Id],
           [@Emp_Sal].[Salary],
           ROW_NUMBER() OVER(ORDER BY [@Department].[Department_Id] ASC, [@Emp_Sal].[Salary] DESC, [@Employee].[Employee_id] ASC) AS 'RowNumber'
      FROM @Employee
     INNER JOIN @Emp_Sal ON [@Employee].[Employee_id] = [@Emp_Sal].[Employee_id]
     INNER JOIN @Emp_Dep ON [@Emp_Sal].[Employee_id] = [@Emp_Dep].[Employee_id]
     INNER JOIN @Department ON [@Emp_Dep].[Department_Id] = [@Department].[Department_Id]
)
SELECT [Main].Employee_id, [Main].Name, [Main].Department, [Main].Salary
FROM Merged [Main]
WHERE [RowNumber] = (SELECT MAX(RowNumber) FROM [Merged] WHERE Department_Id = [Main].Department_Id);

Worth mentioning might be that it's for Microsoft SQL Server!

---

I think the following should work ..
[EDITED]

SELECT 
    ed.department_id,
    d.[name],
    es.employee_id,
    e.[name],
    es.salary
FROM  
    emp_sal es
    INNER JOIN emp_dep ed
        ON ed.employee_id = es.employee_id
    INNER JOIN employee e
        ON e.employee_id = ed.employee_id
    INNER JOIN department d
        ON d.department_id = ed.department_id
WHERE 
  es.salary =   (
                    SELECT 
                        max(iEs.salary)
                    FROM
                        emp_sal iEs
                        INNER JOIN emp_dep iEd
                        ON iEd.employee_id = iEs.employee_id
                    WHERE 
                        iEd.department_id = ed.department_id
                )

I have made it a bit more complex in order to display name of employee and department..

For just the employee ID use

SELECT 
    es.employee_id
FROM  
    emp_sal es
    INNER JOIN emp_dep ed
        ON ed.employee_id = es.employee_id
WHERE 
  es.salary =   (
                    SELECT 
                        max(iEs.salary)
                    FROM
                        emp_sal iEs
                        INNER JOIN emp_dep iEd
                        ON iEd.employee_id = iEs.employee_id
                    WHERE 
                        iEd.department_id = ed.department_id
                )

---

select
    e.employee_id
    d.name as department,
    max(s.salary) as msalary
from
    department as d       
right join emp_dep as e on e.department_id = d.department_id
left join emp_sal as s on s.employee_id = e.employee_id
group by d.name

or something like that