Join two table and get all the employee name with attendance

Question

I have 2 tables First is Employee and Second is Attendance

Employee Table :-

Attendance Table :-

there is only 3 Employee's attendance entered in Attendance Table on AttDate = '2017-09-05'.

i use this query to join these table :-

select EmpName,Attendance from Employee Left join Attendance on Employee.EmpId = Attendance.refId where AttDate ='2017-09-05'

and it's show this output :-

but i need this output ( employees whose attendance did not enter in Attendance Table will also Show with Null attendance ) :-

what should i change in the query to get this output ?

Please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer — a_horse_with_no_name, Sep 05 '17 at 14:51
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? Postgres? Oracle? — a_horse_with_no_name, Sep 05 '17 at 14:51

score 1 · Accepted Answer · answered Sep 05 '17 at 12:53

If you join on the date value as well, the result set will not be eliminated:

select EmpName,Attendance 
from Employee Left join Attendance on Employee.EmpId = Attendance.refId and AttDate ='2017-09-05'

You will also get the employees that have not attended on that day. Check out this answer for more info on the difference between having a condition on the where clause vs having it on the on clause.

score 0 · Answer 2 · answered Sep 05 '17 at 13:03

0

You are restricting it with a WHERE so the two EmpID's who didn't do the course on that day wont show up.

You can change your where to:

WHERE AttDate = '2017-09-05' or AttDate is null;

but that will bring back all results who have a null in the date

answered Sep 05 '17 at 13:03

dbajtr

1,984
2
14
20

Join two table and get all the employee name with attendance

2 Answers2