Q:

Retriving all records from left table and matching records from right

I am trying four queries to retrieve all records from Table1 and matching records from Table2 but my query returns only two records instead of three. 
Table1:

EmpId name
1     xyz1
2     xyz2
3     xyz3

Table2:
EmpId   dateIn
1       2015-05-05
2       2015-05-05

Required result:

EmpId name   DateIn
1     xyz1   2015-05-05
2     xyz2   2015-05-05
3     xyz3   NULL

select Table1.EmpId, Table1.name, Table2.dateIn from Table1 left join Table2 on Table1.empid=Table2.empid where date_format(Datein, '%Y-%m-%d')='2015-05-05'

select Table1.EmpId, Table1.name, Table2.dateIn from Table1 left join Table2 on Table1.empid=Table2.empid where date_format(Datein, '%Y-%m-%d')='2015-05-05' group by Table1.EmpId, Table1.name, Table2.dateIn

select Table1.EmpId, Table1.name, Table2.dateIn from Table1,Table2 where Table1.empid=Table2.empid and date_format(Datein, '%Y-%m-%d')='2015-05-05' group by Table1.EmpId, Table1.name, Table2.dateIn

select Table1.EmpId, Table1.name, Table2.dateIn from Table1,Table2 where Table1.empid=Table2.empid and date_format(Datein, '%Y-%m-%d')='2015-05-05'

Please help.

A:

This will work fine.
Sql Query

declare @Table1 as table( empid tinyint, name varchar(10))
insert into @table1 values (1,'xyz1')
insert into @table1 values (2,'xyz2')
insert into @table1 values (3,'xyz3')

declare @Table2 as table (Empid tinyint, datein date)
insert into @Table2 values(1,'2015-05-05')
insert into @Table2 values(2,'2015-05-05')

select Table1.EmpId, Table1.name, Table2.dateIn from @Table1 Table1 left join @Table2 Table2 on Table1.empid=Table2.empid and '2015-05-05'='2015-05-05'--(or date_format(Datein, '%Y-%m-%d')='2015-05-05')

result

EmpId name       dateIn
----- ---------- ----------
 1     xyz1       2015-05-05
 2     xyz2       2015-05-05
 3     xyz3       NULL

