I have two tables, one called Department and the other called employee;

Department has two field: Deptname and id_dept

Employee has two fields: Empname and id_dept

I want to find out the name of those departments where the number of
employees is the most among all department or one less than the most.

This is my query:

SELECT Deptname FROM (Department AS D INNER JOIN Employee AS E ON
D.ID_DEPT=E.ID_DEPT) GROUP BY Deptname HAVING COUNT(*)>=(SELECT
MAX(Number_of_Employees) FROM (SELECT COUNT (*) AS Number_of_Employees
FROM(Department AS D INNER JOIN Employee AS E ON D.ID_DEPT=E.ID_DEPT) GROUP
BY FILENAME)-1 ;





It seems that there is a lot of redundency.

John Ruan wrote:
That is quite normal for this kind of query. You don't mention your
dbms, but atleast DB2 (and I think SQL server) support Common Table
Expressions which is /sort of/ a named inline view. Using such your
query would become:

-- assuming deptname is unique in department
with dept_cnt (Deptname, cnt) as (
SELECT Deptname, count(*)
FROM Department AS D INNER JOIN Employee AS E
ON D.ID_DEPT=E.ID_DEPT
GROUP BY Deptname
) select Deptname
from dept_cnt
where cnt >= (select max(cnt) - 1 from dept_cnt)

You could of course create a normal view out of dept_cnt and use that
instead.

create view dept_cnt (Deptname, cnt) as (
SELECT Deptname, count(*)
FROM Department AS D INNER JOIN Employee AS E
ON D.ID_DEPT=E.ID_DEPT
GROUP BY Deptname
);

select Deptname
from dept_cnt
where cnt >= (select max(cnt) - 1 from dept_cnt);

As for you query, you can shorten it a bit by not using a join in the
subquery (since all the info you need is in the Employee table)

SELECT Deptname
FROM Department AS D INNER JOIN Employee AS E
ON D.ID_DEPT=E.ID_DEPT
GROUP BY Deptname
HAVING COUNT(*) >= (
SELECT MAX(Number_of_Employees) - 1 FROM (
SELECT COUNT (*) AS Number_of_Employees
FROM Employee AS E
GROUP BY ID_DEPT
) X
)
;