Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Posts
    12

    Answered: Join not getting desired result

    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.

    Any further details provided.

    Thanks

  2. Best Answer
    Posted by gvee

    "
    Code:
    SELECT *
    FROM   dbo.pay_emp_master
     LEFT
      JOIN (
            SELECT *
            FROM   dbo.mhd_month_sheet
            WHERE  calendar_date = '2015-06-16'
           ) As x
        ON x.emp_code = pay_emp_master.emp_code
    ;
    
    --OR
    
    SELECT *
    FROM   dbo.pay_emp_master
     LEFT
      JOIN dbo.mhd_month_sheet
        ON mhd_month_sheet.emp_code = pay_emp_master.emp_code
       AND mhd_month_sheet.calendar_date = '2015-06-16'
    ;
    "


  3. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Share your query, please.
    George
    Home | Blog

  4. #3
    Join Date
    Jul 2010
    Posts
    12
    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

  5. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT *
    FROM   dbo.pay_emp_master
     LEFT
      JOIN (
            SELECT *
            FROM   dbo.mhd_month_sheet
            WHERE  calendar_date = '2015-06-16'
           ) As x
        ON x.emp_code = pay_emp_master.emp_code
    ;
    
    --OR
    
    SELECT *
    FROM   dbo.pay_emp_master
     LEFT
      JOIN dbo.mhd_month_sheet
        ON mhd_month_sheet.emp_code = pay_emp_master.emp_code
       AND mhd_month_sheet.calendar_date = '2015-06-16'
    ;
    George
    Home | Blog

  6. #5
    Join Date
    Jul 2010
    Posts
    12
    Thanks Geeve
    IT worked

  7. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Do you understand why it worked?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •