Find the names of projects where all employees currently working on that project work in the same de-CodePudding

I am new to SQL, and am trying to figure 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 modify our tables, and have to make a query.

"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

"department" - our department table which we can refer to with empid

empid	department
1	1
2	1
3	2
4	3

I've been attempting to make a single query that can do this. Right now I have a found a way to output the number of departments 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.department)
FROM employeeproject ep
LEFT JOIN project p ON p.projid = ep.projid
LEFT JOIN department d ON ep.empid = d.empid
GROUP BY p.projid;

Expected result :

project name
Lord

CodePudding user response：

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'.