Question

I have the following tables in my database: Employee Table which consists of Name, Id , Organization Course Table which consists of CourseName, CourseId

每个雇员都可以学习许多课程,因此我们有许多与男子的关系。为此,我将另设一个表格,即:employee_course,由雇员_id和课程_id组成。

My problem now is: How to get all the employees with their information and all courses names with showing the courses that the employee has taken them?

其结果应给我如下:例如,如果A雇员在服务器管理演播室上第1和第2号课程,我就应当:

雇员A. . 课程表

雇员A. . 课程编号2

我问:


SELECT     dbo.employee.Name, dbo.employee.BadgeNo, dbo.employee.Division, dbo.employee.Organization, dbo.courses.CourseName, 
                      dbo.employee_courses.courseId AS Expr1
FROM         dbo.courses LEFT OUTER JOIN
                      dbo.employee_courses ON dbo.courses.CourseID = dbo.employee_courses.courseId RIGHT OUTER JOIN
                      dbo.employee ON dbo.employee_courses.employeeId = dbo.employee.BadgeNo

Answer 1

2. 以这种形式提出质询:

SELECT
  *
FROM
  employee
LEFT JOIN employee_courses ON (
  dbo.employee_courses.employeeId = dbo.employee.BadgeNo
)
INNER JOIN courses ON (
  dbo.courses.CourseID = dbo.employee_courses.courseId
)

我先把雇员放在桌面上,这肯定是一栏,然后留给另外两栏。

Edit: fixed joins.

Answer 2

SELECT e.EmployeeName, c.CourseName
FROM employee e
LEFT JOIN employee_courses ec on ec.EmployeeID = e.BadgeNo
LEFT JOIN courses c on c.courseId = ec.courseId

页: 1 我先看一栏名,但我先检查一下,而原则是没有意义的。

If you want more specific responses, I d suggest you post some DDL representing the key elements of your database, along with a few INSERTs of representative test data.

友情链接