SQL Server: select query from multiple tables

Question

I got 3 tables: projects, employee and project_employee

employee

ID (int, PK)
Name

projects

project_id (int, PK)
project_name

project_employee

project_id (int, PK)
employee_id (int, PK)

What I trying to do is write a query that get ID and Name of all employees that are not in a project, for example project number 9.

So I tried:

SELECT ID, Name 
FROM [employee], [project_employee] 
WHERE [employee].ID != [project_employee].emp_id AND [project_employee].project_id = 9;

but I always get empty result, something must be wrong with my logic?

Shouldn't it be [employee].ID = [project_employee].emp_id AND [project_employee].project_id != 9 instead? — Michael McGriff
– Michael McGriff, Commented Jun 12, 2014 at 19:34

souslov · Accepted Answer · 2014-06-12 20:14:05Z

2

You can do it using NOT EXISTS :

SELECT u.ID, u.Name 
FROM [User] u
WHERE NOT EXISTS ( SELECT *
                   FROM [project_employee] pe
                   WHERE pe.project_id = 9 
                     AND pe.employee_id = u.ID);

answered Jun 12, 2014 at 20:14

souslov

44.7k11 gold badges92 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2989408 · Accepted Answer · 2014-06-12 19:34:45Z

2

Instead try something like this, it should get you all users that are not part of project_id = 9

SELECT u.ID, u.Name 
FROM [User] u
WHERE u.ID NOT IN (SELECT pe.employee_id
                  FROM [project_employee] pe
                  WHERE pe.project_id = 9);

answered Jun 12, 2014 at 19:34

user2989408

3,1371 gold badge20 silver badges15 bronze badges

Comments

Kevin Cook · Accepted Answer · 2014-06-12 19:51:12Z

2

SELECT ID, NAME 
FROM employee e
LEFT JOIN project_employee pe
    ON pe.employee_id = e.id
      AND pe.project_id = 9
WHERE pe.employee_id IS NULL

You don't even need projects table since you are not looking for employees on a project.

edited Jun 12, 2014 at 19:51

answered Jun 12, 2014 at 19:41

Kevin Cook

1,9321 gold badge15 silver badges16 bronze badges

2 Comments

Olivier Jacot-Descombes Over a year ago

The OP wants employees who are not in a specific project. For example project number 9, he says.

Kevin Cook Over a year ago

ok, added a specific one, still don't have to use all 3 tables.

Rahul · Accepted Answer · 2014-06-12 20:12:38Z

2

You can use a LEFT JOIN to get the required data

select e.id,e.name
from employee e
left join project_employee pe
on e.id = pe.employee_id
left join projects p
on pe.project_id = p.project_id
where p.project_id is null

(OR) Just left join with project_employee table. No condition has been checked assuming that all employees with no project at hand is the desired output.

select e.id,e.name
from employee e
left join project_employee pe
on e.id = pe.employee_id
where pe.project_id is null

edited Jun 12, 2014 at 20:12

answered Jun 12, 2014 at 19:35

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

5 Comments

Olivier Jacot-Descombes Over a year ago

Table projects is not required in order to get the result. Also this will only return employees who are in a project with no name, because the project_employee table can have only non null entries because all the fields are in the primary key and because probably there is a foreign key on project_id. I.e. for every project in project_employee there is a record in projects.

Rahul Over a year ago

@OlivierJacot-Descombes, that's the hole query which will work even if OP wants to fetch project_name. I didn't checked for the condition project_id = 9 cause that was not the intention probably as OP mentioned in post saying that an example (probably he wants to get all employee with no project).

Olivier Jacot-Descombes Over a year ago

Because of the inner join the query will only return employees with projects, so the query is wrong anyway.

Rahul Over a year ago

@OlivierJacot-Descombes, that's a hurry mistake. thanks for pointing.

Rahul Over a year ago

@OlivierJacot-Descombes, edited the answer to be per with the posted question. Thanks.

Greg · Accepted Answer · 2014-06-12 19:53:36Z

1

He asked for the ability to check that the employees are not in a specific project. The not in solution is fine, but I generally prefer using left joins.

SELECT ID, NAME 
FROM employee e
LEFT JOIN project_employee pe
  ON pe.emp_id = e.id
  and pe.project_id = 9
WHERE pe.employee_id IS NULL

answered Jun 12, 2014 at 19:53

Greg

2,70523 silver badges28 bronze badges

Collectives™ on Stack Overflow

SQL Server: select query from multiple tables

5 Answers 5

Comments

Comments

2 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related