SQLTeam.com | Weblogs | Forums

Join not getting desired result


#1

I have a right join query where i have a table where all employees attendance entry are registered and another table where pay master table where al employees details.

I need a query where i get all employee names from right side (pay master table) and the employees in out timings for all employees who punched or not punched their card.
means for selected date i need all the employees details suppose 10 employees who punched or not with empty values even he resigned.

I used right or left join it gives all dates value but when i select particular date i get only the punched details.

SELECT TOP (100) PERCENT dbo.MHD_MONTH_SHEET.Calender_Date, dbo.MHD_MONTH_SHEET.Dept_Code, dbo.MHD_MONTH_SHEET.Emp_Code,
dbo.MHD_MONTH_SHEET.Emp_Name, dbo.Pay_Emp_Master.Emp_Code AS empcode, dbo.Pay_Emp_Master.Emp_First_Name,
dbo.Pay_Emp_Master.Emp_Middle_Name
FROM dbo.MHD_MONTH_SHEET LEFT OUTER JOIN
dbo.Pay_Emp_Master ON dbo.MHD_MONTH_SHEET.Emp_Code = dbo.Pay_Emp_Master.Emp_Code
ORDER BY dbo.MHD_MONTH_SHEET.Dept_Code, dbo.MHD_MONTH_SHEET.Emp_Code, dbo.MHD_MONTH_SHEET.Calender_Date

we have total employee 553 in pay emp master, i need all the employees details in and out even if the not punched for specific date

Hope good

Calender Date depcode Emp Name paymaster.Name Dcode Name
1/3/2013 12:00:00 AM 1 221 Aniceto Pinto NULL NULL NULL
1/5/2013 12:00:00 AM 1 221 Aniceto Pinto NULL NULL NULL
1/6/2013 12:00:00 AM 1 221 Aniceto Pinto NULL NULL NULL
1/7/2013 12:00:00 AM 1 221 Aniceto Pinto NULL NULL NULL
1/8/2013 12:00:00 AM 1 221 Aniceto Pinto NULL NULL NULL
2/14/2012 12:00:00 AM 1 721 Mohamed Azim Abdul Rahman Patil 721 Mohamed Azim Abdul Rahman Patil
2/15/2012 12:00:00 AM 1 721 Mohamed Azim Abdul Rahman Patil 721 Mohamed Azim Abdul Rahman Patil
2/16/2012 12:00:00 AM 1 721 Mohamed Azim Abdul Rahman Patil 721 Mohamed Azim Abdul Rahman Patil
2/18/2012 12:00:00 AM 1 721 Mohamed Azim Abdul Rahman Patil 721 Mohamed Azim Abdul Rahman Patil

Thanks


#2

Try it the other way around:

SELECT TOP (100) PERCENT ms.Calender_Date
     , ms.Dept_Code
     , ms.Emp_Code
     , ms.Emp_Name
     , em.Emp_Code AS empcode
     , em.Emp_First_Name
     , em.Emp_Middle_Name
FROM dbo.Pay_Emp_Master em
LEFT JOIN dbo.MHD_MONTH_SHEET ms
     ON ms.Emp_Code = em.Emp_Code
ORDER BY ms.Dept_Code
     , ms.Emp_Code
     , ms.Calender_Date

#3

Thanks gbritton
It worked.