CODEWITHSUNDEEP
sql-servert-sql
Manju
Manju

Reputation: 2650

SQL joins with left outer join

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,

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,

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

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

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,

enter image description here

Upvotes: 2

Views: 90

Answers (3)

user7715598
user7715598

Reputation: 

--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

Upvotes: 0

Fabio Campinho
Fabio Campinho

Reputation: 1022

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

Upvotes: 1

gsharp
gsharp

Reputation: 27937

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?

Upvotes: 2

Related Questions