SQL Joins: Select status of reviews submitted by employees and also the list of employees who have not submitted the review for each year

Viewed 156

For each year, for each employee, I want to list the status of the review that employee had submitted, or "not initiated" in case employee did not submit review for that year.

It's kind of difficult to express the question in words therefore I would try to explain it by giving example:

create table #employees 
(
empid int,
name varchar(100)
)

Create table #review
(
empid int,
ryear int,
status varchar(20)
)

insert into #review values(1,2016,'S2')
insert into #review values(2,2016,'S2')
insert into #review values(2,2017,'S1')
insert into #review values(3,2017,'S2')



insert into #employees values(1,'jack')
insert into #employees values(2,'mack')
insert into #employees values(3,'rack')
insert into #employees values(4,'tack')

Wrong Query

select a.empid
      ,a.name
      ,b.ryear
      ,case isnull(b.status,'')
           when ''
               then 'Not Initiated'
           else status
       end as status
from #employees as a
    left join #review as b
        on a.empid = b.empid
           and b.ryear in(select distinct
                                 ryear
                          from #review
                         );--something like that

Expected Result:

+-------+------+-------+----------------+
| empid | name | ryear |     status     |
+-------+------+-------+----------------+
|     1 | jack |  2016 | S2             |
|     1 | jack |  2017 | not initiated  |
|     2 | mack |  2016 | S2             |
|     2 | mack |  2017 | S1             |
|     3 | rack |  2016 | not initieated |
|     3 | rack |  2017 | S2             |
|     4 | tack |  2016 | Not Initiated  |
|     4 | tack |  2017 | Not Initiated  |
+-------+------+-------+----------------+
3 Answers
Related