SELECT tblSign.sigdate,tblSign.sigtime,tblSign.sigact,tblSign.esignature,tblEmpl.fname,tblEmpl.lname,tblEmpl.location, tblEmpl.estatus,tblLocs.unit,tblLocs.descript,TblLocs.addr1,tblLocs.city,tblLocs.state, tblLocs.zip
   FROM tblEmpl
   LEFT JOIN tblSign
ON tblSign.eight_id = tblEmpl.eight_id
AND tblSign.formid = '9648'
AND tblSign.sigact <> 'O'
AND tblSign.sigdate >= '2022-11-01'
LEFT JOIN tblLocs
ON tblEmpl.location = tblLocs.location
WHERE tblEmpl.estatus = 'A'
AND tblEmpl.location = '013'
ORDER BY
tblSign.sigdate ASC;

My table Sign has multiple records with the same eight_id so Im just trying to join tables getting the most recent record from tblSign besides multiple records

Data I get

This is what I want :

CodePudding user response：

Start by getting into better code-writing habits. Having all column names in one long string is horrible for readability and consequently troubleshooting. You can select the most recent record from a table by using a ROW_NUMBER function. I took your code, cleaned it up, added a derived table and in the derived table added a ROW_NUMBER function. I can't validate that the query works because you didn't post example source data from your tblEmpl, tblSign, and tblLocs tables. I'm not sure if the AND tblSign.sigact <> 'O' is valid in the derived table because it's not clear if you were trying to just limit the date range or that was your attempt to retrieve the most recent date.

SELECT 
    tblSign.sigdate
    , tblSign.sigtime
    , tblSign.sigact
    , tblSign.esignature
    , tblEmpl.fname
    , tblEmpl.lname
    , tblEmpl.location
    , tblEmpl.estatus,tblLocs.unit
    , tblLocs.descript
    , TblLocs.addr1
    , tblLocs.city
    , tblLocs.state
    , tblLocs.zip
FROM tblEmpl
    LEFT JOIN (
        SELECT *

            --Used to order the records for each eight_id by the date. 
            --Most recent date for each eight_id will have row_num = 1.
            , ROW_NUMBER() OVER(PARTITION BY eight_id ORDER BY sigdat DESC) as row_num
        FROM tblSign as ts
        WHERE tblSign.formid = '9648'
            AND tblSign.sigact <> 'O'
            AND tblSign.sigdate >= '2022-11-01' --Not clear if this is just to limit results or an attempt to get most recent date in the failed original code.
    ) as ts
        ON ts.eight_id = tblEmpl.eight_id
        AND ts.row_num = 1  --Use to limit to most recent date.
    LEFT JOIN tblLocs
        ON tblEmpl.location = tblLocs.location
WHERE tblEmpl.estatus = 'A'
    AND tblEmpl.location = '013'
ORDER BY
    tblSign.sigdate ASC

CodePudding user response：

You use ROW_NUMBER to get the last entry in my case for every esignature, as i thought this must be unique

WITH CTE AS
(SELECT 
    tblSign.sigdate,
    tblSign.sigtime,
    tblSign.sigact,
    tblSign.esignature,
    tblEmpl.fname,
    tblEmpl.lname,
    tblEmpl.location,
    tblEmpl.estatus,
    tblLocs.unit,
    tblLocs.descript,
    TblLocs.addr1,
    tblLocs.city,
    tblLocs.state,
    tblLocs.zip,
    ROW_NUMBER() OVER(PARTITION BY tblSign.esignature ORDER BY tblSign.sigdate DESC) rn
FROM
    tblEmpl
        LEFT JOIN
    tblSign ON tblSign.eight_id = tblEmpl.eight_id
        AND tblSign.formid = '9648'
        AND tblSign.sigact <> 'O'
        AND tblSign.sigdate >= '2022-11-01'
        LEFT JOIN
    tblLocs ON tblEmpl.location = tblLocs.location
WHERE
    tblEmpl.estatus = 'A'
        AND tblEmpl.location = '013')
SELECT     sigdate,
    sigtime,
    sigact,
    esignature,
    fname,
    lname,
    location,
    estatus,
    unit,
    descript,
    addr1,
    city,
    state,
    zip
WHERE rn = 1
ORDER BY sigdate ASC;