• mcfarlandparkway - Tuesday, March 28, 2017 2:28 PM

    How to join Employee and Student tables with HR table, - using ID and date columns to join with effective dates of the HR; such that date is between HR job record effective dates.

    EMployee table
    EmpID
    RecDate

    Student Table
    StdID
    SevDate

    HR table
    HRID
    VaFrmDate
    VaToDate

    I assume that HR table is the parent table, and that not all HR is in EMployee AND Student.
    Without more information and data to validate, my guess would be something like this.

    SELECT
      h.HRID
    , h.VaFrmDate
    , h.VaToDate
    , e.RecDate
    , s.SevDate
    FROM HR AS h
    LEFT JOIN EMployee AS e
     ON h.HRID = e.EmpID
    AND e.RecDate >= h.VaFrmDate
    AND e.RecDate <= h.VaToDate
    LEFT JOIN Student AS s
     ON s.StdID = e.EmpID
    AND s.SevDate >= h.VaFrmDate
    AND s.SevDate <= h.VaToDate;