15,745,433 members
See more:
Hey i have 3 tables Many to many related ... EmpDept is the junction table between Employee and Department ...

1.Employee
2.Department
3.EmpDept

Employee has 2 coloumns: EmpID EmpName
1.EmpID (Primary Key) 1 Robert
2.EmpName 2 Alex
3 Kathie

Department has 2 : DeptID DeptName
1.DeptID (Primary Key) 1 Human Resource
2.DeptName 2 Computer
3 English

EmpDept : EmpID DeptID
1.EmpID 1 1
2.DeptID 1 2
2 1
2 2

Now the task is to write such query an employee can work in multiple departments and and a department has multiple employees ??
Now write query for :-
if i select EmpID 1 it should display me Human Resource and Computer
& if i select DeptID 2 it should dispaly Robert and Alex
Posted
Sandeep Mewara 13-May-12 15:24pm
Did you try by yourself at all?

## Solution 1

To get employee:
SQL
```SELECT E.*, D.*
FROM Employee AS E
LEFT JOIN EmpDept AS ED ON E.EmpID  = ED.EmpID
LEFT JOIN Department AS D ON ED.DeptID  = D.DeptID
WHERE E.EmpId = @EmpId```

To get Deparment:
SQL
```SELECT E.*, D.*
FROM Department AS D
LEFT JOIN EmpDept AS ED ON ED.DeptID  = D.DeptID
LEFT JOIN Employee AS E ON ED.EmpID  = E.EmpID
WHERE D.DeptId = @DeptId```

v3
Manas Bhardwaj 13-May-12 15:35pm
my 5!
Maciej Los 13-May-12 15:42pm
Thank you, Manas ;)
Sandeep Mewara 14-May-12 4:56am
My 5!
Maciej Los 14-May-12 7:14am
Thank you, Sandeep ;)
Member 14511244 25-Jun-19 0:35am
Thanks Maciej Los, 5 stars!

Top Experts
Last 24hrsThis month
 OriginalGriff 50 Richard Deeming 10 PaulaJoannAllen -12 Zehlm Web Development, LLC -16
 OriginalGriff 1,883 Richard MacCutchan 1,358 merano99 609 Rick York 455 Graeme_Grant 360

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900