locked
Join problem RRS feed

  • Question

  • User-373571878 posted

    Hello,

    I have two tables. Each record contains a unique EmpId for a particular WeekEndDate.  (Each Table has a WeekEndDate).

    I want to join the two tables together and return the rows if they have  the same weekenddate (which I send as a parameter) and also the matching EmpId.

    However, if TableA has a record matching the supplied date and TableB does not I still want to return those rows in TableA even if there are no matching records in TableB

    And return the row in Table B if there are no matching record in Table A

    I was trying to us a full outer join, but had no success. 

    I tried this, but it returned the same TableB record on all TableA records

    from PayrollHours PH Full join #Temp_TOTAL_MTC_HOURS MH
    ON Ph.EmployeeID = MH.EmployeeID
    Where PH.WeekEndDate = '01/26/2019' or MH.WeekEndDate = '01/26/2019'

    Can you help?

    Friday, February 8, 2019 3:24 PM

Answers

  • User753101303 posted

    Hi,

    PH.WeekEndDate or MY.WeekEnddate could be NULL and you want to match on those values in the general case so IMO you want :

    from PayrollHours PH Full join #Temp_TOTAL_MTC_HOURS MH
    ON Ph.EmployeeID = MH.EmployeeID AND PH.WeekEndDate=MH.WeekEndDate -- mtach both sides as needed with NULL if it doesn't match

    -- Add a selection criteria as needed :
    Where PH.WeekEndDate = '01/26/2019' or MH.WeekEndDate = '01/26/2019'

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 8, 2019 4:06 PM

All replies

  • User475983607 posted

    I think you need a GROUP BY UNION query not a join.

    IF OBJECT_ID('tempdb..#TableA') IS NOT NULL
    	DROP TABLE #TableA
    
    IF OBJECT_ID('tempdb..#TableB') IS NOT NULL
    	DROP TABLE #TableB
    
    CREATE TABLE #TableA (EmpId INT, WeekEndDate DATETIME)
    CREATE TABLE #TableB (EmpId INT, WeekEndDate DATETIME)
    
    INSERT INTO #TableA(EmpId, WeekEndDate)
    VALUES (1, '10/1/2018'),
    (1, '11/1/2018'),
    (1, '12/1/2018'),
    (1, '1/1/2019'),
    (2,'1/1/2019'),
    (3,'1/1/2019')
    
    INSERT INTO #TableB(EmpId, WeekEndDate)
    VALUES (1, '10/1/2018'),
    (1, '11/1/2018'),
    (1, '12/1/2018'),
    (2, '1/1/2019'),
    (2,'1/1/2019'),
    (1,'1/1/2019')
    
    SELECT t.EmpId, t.WeekEndDate FROM (
    SELECT a.EmpId, a.WeekEndDate
    FROM #TableA AS a
    WHERE a.WeekEndDate = '1/1/2019'
    UNION ALL
    SELECT b.EmpId, b.WeekEndDate
    FROM #TableB AS b
    WHERE b.WeekEndDate = '1/1/2019'
    ) AS t
    GROUP BY t.EmpId, t.WeekEndDate 
    
    

    Can you post sample table schema and data that reproduces the issue.  Also can you explain why the design has redundant WeekEndDate columns?

    Friday, February 8, 2019 3:56 PM
  • User753101303 posted

    Hi,

    PH.WeekEndDate or MY.WeekEnddate could be NULL and you want to match on those values in the general case so IMO you want :

    from PayrollHours PH Full join #Temp_TOTAL_MTC_HOURS MH
    ON Ph.EmployeeID = MH.EmployeeID AND PH.WeekEndDate=MH.WeekEndDate -- mtach both sides as needed with NULL if it doesn't match

    -- Add a selection criteria as needed :
    Where PH.WeekEndDate = '01/26/2019' or MH.WeekEndDate = '01/26/2019'

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 8, 2019 4:06 PM
  • User-373571878 posted

    Thanks Mgebhard,

    TableA
    Empid
    WeekendDAte
    Crew
    PHWorkHours
    PHTotHours

    TableB
    EMPID
    WeekendDAte
    Crew
    MHMTCHours
    MHPunchMinutes

    Expected output:

    Not sure what table the EmpID, WeekendDate, and crew would come from if one table returns no rows?

    EmpId.....WeekEndDate.....Crew......PHWorkHours....PHTotHours....MHMTCHours, MHPunchMinutes,  
    123456...'01/26/2019'.......88844..........40.0...........................45.0................... 35.0............................. 2100

    123456....'01/26/2019'......99922..............0................................0...........................27.5...........................1650

    144522....'01/26/2019'.......53242...........35.0........................40.0..........................0................................0

    Does this make sense?

    Friday, February 8, 2019 4:28 PM
  • User-373571878 posted

    thanks PatriceSc,

    I think I am closer with your suggestion.   In Fact I think It may have worked, is there any holes I should be aware of?

    Here is the complete query

    Select case when (PH.employeeId) is null then MH.EmployeeId else Ph.EmployeeID end as [Employee Id],
    Case when (CONVERT(varchar,PH.WeekEndDate,6)) is null then (CONVERT(varchar,MH.WeekEndDate,6)) else (CONVERT(varchar,PH.WeekEndDate,6)) end WeekEndDate,
    Case when PH.Crew is null then MH.Crew else PH.Crew end Crew,
    Case When (PH.StdHours + PH.OTHrs + PH.Dthrs) is null then 0 else (PH.StdHours + PH.OTHrs + PH.Dthrs) end as WorkHours,
    Case when (PH.TotHrs) is null then 0 else ph.TotHrs end as TotHrs,
    Case when (CAST(((PH.StdHours + PH.OTHrs + PH.Dthrs) * 60) AS INT)) is null then 0 else (CAST(((PH.StdHours + PH.OTHrs + PH.Dthrs) * 60) AS INT)) end as TotPayRollMinutes,
    'PAY' as QueryPart,
    Case when (MH.TotMTCHrs) is null then 0 else MH.TotMTCHrs end as TotMTCHrs,
    Case when (MH.PunchMinutes) is null then 0 else MH.PunchMinutes end as PunchMinutes,
    'MTC' QueryPart2

    from PayrollHours PH Full join #Temp_TOTAL_MTC_HOURS MH
    ON Ph.EmployeeID = MH.EmployeeID AND PH.WeekEndDate=MH.WeekEndDate
    Where PH.WeekEndDate = '04/14/2018' or MH.WeekEndDate = '04/14/2018'
    order by Ph.employeeid

    Thank you!

    Friday, February 8, 2019 4:35 PM