Query to filter by a count

Given these tables:

Employee(ssn, name, sex, address, salary, 开发者_运维问答bdate, dno, superssn)
fk:superssn is ssn in Employee
fk:dno is dnumber in Department

Department(dnumber, dname, mgrssn, mgrstartdate)
fk:mgrssn is ssn in Employee

Dept_locations(dnumber, dlocation)
fk:dnumber is dnumber in Department

Project(pnumber, pname, plocation, dnum)
fk:dnum is dnumber in Department

Dependent(essn, dependent_name, sex, bdate, relationship)
fk: essn is ssn in Employee

Works_on(essn,pno,hours)
fk: essn is ssn in Employee; pno is pnumber in Project

I need to retrive the name of each department as well as the departments manger's name for departments that have two locations...

I have this query so far

SELECT D.dname, D.mgrssn
FROM department D, dept_locations DL
WHERE D.dnumber = dl.dnumber
Group by D.dname, D.mgrssn

I now get the three non duplicating departments. How do I restrict this to departments which have two locations?

---

SELECT D.dname, D.mgrssn
FROM Department D, Dept_locations DL
WHERE D.dnumber = DL.dnumber
GROUP BY D.dname, D.mgrssn
HAVING COUNT(*) >= 2            --- for 2 or more locations

and it's better to use INNER JOIN (or simply JOIN )instead of WHERE for joining related tables. Note the diference from the previous query. Both will return same results :

SELECT D.dname, D.mgrssn
FROM Department D
    JOIN Dept_locations DL
        ON D.dnumber = DL.dnumber
GROUP BY D.dname, D.mgrssn
HAVING COUNT(*) >= 2          

If you also want to show the manager's name, you'll have to join the Employee table as well (and group accordingly):

SELECT D.dname
     , D.mgrssn
     , E.name
FROM Department D
    JOIN Employee E 
        ON E.ssn = D.mgrssn
    JOIN Dept_locations DL
        ON D.dnumber = DL.dnumber
GROUP BY D.dname
       , D.mgrssn
       , E.name
HAVING COUNT(*) >= 2       

---

For exactly two locations:

SELECT d.name AS dept_name,
       e.name AS mgr_name
  FROM DEPARTMENT d
  JOIN EMPLOYEE e ON e.ssn = d.mgrssn
 WHERE EXISTS (SELECT NULL
                 FROM DEPT_LOCATIONS dl
                WHERE dl.dnumber = d.dnumber
             GROUP BY dl.dnumber
               HAVING COUNT(*) = 2)

For two or more locations:

SELECT d.name AS dept_name,
       e.name AS mgr_name
  FROM DEPARTMENT d
  JOIN EMPLOYEE e ON e.ssn = d.mgrssn
 WHERE EXISTS (SELECT NULL
                 FROM DEPT_LOCATIONS dl
                WHERE dl.dnumber = d.dnumber
             GROUP BY dl.dnumber
               HAVING COUNT(*) >= 2)

Keep in mind

The DEPARTMENT table has a mgrstartdate, so you'll need to check against it -- the two examples I provided will show you all the historical managers in addition to the current managers because there's no filtering on the mgrstartdate.

---

select dname, name
from Department, Employee, Dept_locations
where mgrssn = ssn
and Department.dnumber = Dept_locations.dnumber