SQL连接与左外部连接

I'm trying to fetch an extra column from a table with a join query on one of the table which are in left outer join,

我试图从表中获取一个额外的列其中一个表的连接查询位于左外连接中，

PFB my tables,

PFB我的表,

Create table tblEmployee(
ID int identity primary key,
FirstName nvarchar(50),
LastName nvarchar(50),
Gender nvarchar(50),
Salary int
)

Create table tblCourses(
CourseId int identity primary key,
CourseName nvarchar(50) not null
)


Create table tblEmpCourses(
EmpCourseSlNo int identity primary key,
EmpID int foreign key references tblEmployee(ID),
CourseID int foreign key references tblCourses(CourseId)
)

PFB the insertion statements,

PFB插入语句,

insert into tblEmployee values ('FName1', 'LName1', 'Male', '10000')
insert into tblEmployee values ('FName2', 'LName2', 'Male', '15000')
insert into tblEmployee values ('FName3', 'LName3', 'Female', '30000')
insert into tblEmployee values ('FName4', 'LName4', 'Male', '50000')
insert into tblEmployee values ('FName5', 'LName5', 'Male', '60000')
insert into tblEmployee values ('FName6', 'LName6', 'Female', '0000')
insert into tblEmployee values ('FName7', 'LName7', 'Male', '10000')
insert into tblEmployee values ('FName8', 'LName8', 'Male', '30000')

insert into tblCourses values ('Asp.net')
insert into tblCourses values ('C#')
insert into tblCourses values ('WebAPI')
insert into tblCourses values ('SQL')
insert into tblCourses values ('WCF')
insert into tblCourses values ('JS')
insert into tblCourses values ('CSS')
insert into tblCourses values ('JAVA')

insert into tblEmpCourses values (1, 1)
insert into tblEmpCourses values (1, 2)
insert into tblEmpCourses values (1, 3)
insert into tblEmpCourses values (1, 4)

How to write a query to fetch the data with all employee firstName where he has subscribed to any course or not along with courseID and courseName

如何编写一个查询来获取所有员工的姓名，包括他是否订阅了任何课程以及课程id和courseName

Trying with the below query, but not able to add CourseName in the select statement, how to add one more join here b/w tblEmpCourses table and tblCourses with left_outer_join to fetch the CourseName

尝试使用下面的查询，但是无法在select语句中添加CourseName，如何在这里添加一个连接b/w tblEmpCourses表和带有left_outer_join的tblCourses来获取CourseName

So that i want the data as

我想要的是

FirstName CourseID CourseName

  select FirstName, CourseID 
  from   tblEmployee 
  left outer join tblEmpCourses 
  on     tblEmployee.ID = tblEmpCourses.EmpID;

Result expected as per the below snapshot,

预期结果如下快照所示，

3 个解决方案

#1

select FirstName, CourseID, CourseName
from   tblEmployee 
left outer join tblEmpCourses 
on     tblEmployee.ID = tblEmpCourses.EmpID
left outer join tblCourses
on tblEmpCourses.CourseId = tblCourses.CourseId

#2

SELECT
 e.FirstName,
 c.CourseID,
 c.CourseName
FROM tblEmployee e
LEFT JOIN tblEmpCourses ec ON e.ID =ec.EmpID
LEFT JOIN tblCourses  c ON c.CourseID = ec.CourseID

something like that?

类似的东西吗?

#3

--Inner join for matching records
 SELECT FirstName, C.CourseID ,tC.CourseName
  FROM   tblEmployee E
  INNER JOIN 
    tblEmpCourses C 
      ON     E.ID = C.EmpID
  INNER join tblCourses tc
    ON tc.CourseId=C.CourseID

OutPut

输出

FirstName   CourseID    CourseName
FName1      1          Asp.net
FName1      2          C#
FName1      3          WebAPI
FName1      4          SQL

#1

select FirstName, CourseID, CourseName
from   tblEmployee 
left outer join tblEmpCourses 
on     tblEmployee.ID = tblEmpCourses.EmpID
left outer join tblCourses
on tblEmpCourses.CourseId = tblCourses.CourseId

#2

SELECT
 e.FirstName,
 c.CourseID,
 c.CourseName
FROM tblEmployee e
LEFT JOIN tblEmpCourses ec ON e.ID =ec.EmpID
LEFT JOIN tblCourses  c ON c.CourseID = ec.CourseID