CODEWITHSUNDEEP
sqlsql-server
Kokirala Sudheer
Kokirala Sudheer

Reputation: 439

How to get all departments with Employee number

I have an EmployeeDepartmetn juction table like this. I have all the departments in Depeartment table and employees in Employee table..enter image description here

I want to get departments for an particular employee along with the all the departments available in depeartment table.

It should be like Select DepartmentId, DepartmentName, EmployeeID from Query.

Main criteria here is, Need to display NULL if the employee dont have that department. I am confused here...please help.

Please give Linq Query

Thanks in Advance

Upvotes: 0

Views: 816

Answers (2)

APH
APH

Reputation: 4154

Put criteria in your left join:

Select distinct a.DeptID, b.DepartmentName, b.EmployeeID
From Department a
left join EmployeeDepartment b
on a.DeptID = b.DeptID and b.EmployeeID = 1 --insert employee ID here

It will show all departments (even those with no employees), then show the employee ID you chose in the third column only if that employee is assigned there.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can do this with conditional aggregation:

select DeptId,
       max(case when EmployeeId = 1 then EmployeeId end) as EmployeeId
from EmployeeDepartment ed
group by DeptId;

EDIT:

If you have a departments table as well:

select d.deptid, d.name, ed.employeeid
from Departments d left join
     EmployeeDepartment ed
     on d.deptid = ed.deptid and
        ed.employeeid = 1;

Upvotes: 0

Related Questions