SQLTeam.com | Weblogs | Forums

Outer join question


#1

I have a table named T_LevelsApproved which has an Emp No., a date the Emp was approved, and an ID for the T_Level itself. I have another table named T_Levels which lists all T_Levels. It has an ID for the T_Level and the description for the T_Level. I need to retrieve all records from T_Levels and also show the T_Levels an employee has been approved for when I pass the Emp No. to the sql string.

Employees may have taken some but not all T_Levels and so I need to show all T_Levels and the dates for each of the T_Levels an employee has taken and have the dates for the T_Levels the employee has NOT taken to be blank when I pass the Emp No. to the sql string.

T_Levels has 5 records, Level_1, Level_2, etc

For example, employee Joe has taken T_Levels 1, 2, and 5 and has not taken T_Levels 3 and 4. So, I need the query to retun the data below when I pass the Joe's Emp No. to the sql string

Joe
T_Level Date
Level_1 07/01/2015
Level_2 07/02/2015
Level_3
Level_4
Level_5 07/05/2015

Another example, employee Same has taken T_Levels 2, 3, and 4 and has NOT taken T_Levels 1 and 5. So, I need the query to retun the data below when I pass the Sam's Emp No. to the sql string

Sam
T_Level Date
Level_1
Level_2 07/02/2015
Level_3 07/03/2015
Level_4 07/04/2015
Level_5

If an employee has NOT been approved for any T_Levels then I need to return the data below when I pass that employee's Emp No. to the sql string

T_Level Date
Level_1
Level_2
Level_3
Level_4
Level_5

If an employee has been approved for ALL T_Levels then I need to return the data below when I pass that employee's Emp No. to the sql string

T_Level Date
Level_1 07/01/2015
Level_2 07/02/2015
Level_3 07/03/2015
Level_4 07/04/2015
Level_5 07/05/2015


#2

drop table T_LevelsApproved
create table T_LevelsApproved ( EmpNo int, Date datetime,ID int,Name varchar(50))
insert into T_LevelsApproved (EmpNo,Date,ID,Name)
values(1,'20150107',1,'Joe'),
(1,'20150207',2,'Joe'),
(1,'20150507',5,'Joe'),
(2,'20150207',2,'Sam'),
(2,'20150307',3,'Sam'),
(2,'20150407',4,'Sam')

drop table T_Levels
create table T_Levels ( ID int, Decription varchar(50),T_Levels varchar(50))
insert into T_Levels (ID,Decription,T_Levels)
values
(1,'YO','Level_1'),
(2,'SQL','Level_2'),
(3,'KING','Level_3'),
(4,'THEHR','Level_4'),
(5,'GOOGLE','Level_5')

select Name,T_Levels,DATE from T_Levels a
left join T_LevelsApproved b on a.ID = b.ID