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.
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments