I am new to sql, and am trying to firgure our how to "find the names of projects where all employees currently working on the project work in the same department." For this problem, we have three tables. This problem could be simplified if this data was stored in fewer tables. But lets assume we can’t modifity our tables, and have to make a queery.
"employeeproject"
Our project table that we can refer to with our empid
| empid | projectid |
|---|---|
| 1 | 2 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
"project"
Project table, that we can refer to with projectid
| projectid | name |
|---|---|
| 1 | ring |
| 2 | lord |
"deparment"
Our deparment table which we can refer to with empid
| empid | deparment |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
I’ve been attepting to make a single querry that can do this. Right now I have a found a way to output the number of deparments working on a project, but I still need to get the name of the project to output. Any suggestions would help.
SELECT count(DISTINCT d.deprment)
FROM employeeproject ep
LEFT JOIN project p
on p.projid = ep.projid
LEFT JOIN deparment d
on ep.empid = d.empid
group by p.projid;
Expected result :
| project name |
|---|
| Lord |
>Solution :
You can use an aggregation on the project name while using count of different departments equal to 1 as condition in an HAVING clause.
SELECT name
FROM project p
INNER JOIN employeeproject ep
ON p.projectid = ep.projectid
INNER JOIN department d
ON ep.empid = d.empid
GROUP BY name
HAVING COUNT(DISTINCT d.deparment) = 1
Here’s a demo in MySQL, though this should work on most DBMS’.