sql查询显示表内容多对多关系 [英] Sql Queries to show contents of the table many to many relationship

问题描述

嘿，我有3个表，许多相关... EmpDept是员工和部门之间的连接表...

1.员工
2.部门
3.EmpDept


员工有2个栏目:EmpID EmpName
1.EmpID(主键)1罗伯特
2.EmpName 2亚历克斯
3凯蒂



部门有2个:DeptID DeptName
1.DeptID(主键)1人力资源
2.DeptName 2计算机
3英语


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


现在的任务是编写这样的查询:一个雇员可以在多个部门工作，而一个部门有多个雇员? 现在编写查询:-
如果我选择EmpID 1，则应显示人力资源和计算机"
&如果我选择DeptID 2，则应该显示Robert和Alex

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

推荐答案

要获得员工:

To get employee:

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:

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

这篇关于sql查询显示表内容多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！