Connie DeCinko Connie DeCinko - 5 months ago 12
SQL Question

Query to return values from table B when newer than record in Table A

I have a table with a member's name, address, etc. and a time stamp of the last time the record was updated. I have a second table that holds updates to the member record, a holding table, until changes are approved by staff.

I have a query that returns data from the member table. I now need to check the updates table, and if the member's record in the updates table has a more recent time stamp, return that record instead of the record in the member table.

I tried a few things such as a

UNION
with
Top 1
but it's not quite right. I could make a complex
CASE
statement but is that going to perform well?

It sounds simple, get the most recent record from table A, and the most recent from table B and return the one record that is the newest.

SELECT name, address, city, state, zipcode, time_stamp
FROM Member
WHERE ID = 123

SELECT name, address, city, state, zipcode, time_stamp
FROM MemberUpdates
WHERE ID = 123


EDIT:

OK, with the help so far, I was able to get the results I expected. Then, I went to add the extra where clauses and I broke it. Tried several different ways including using a CTE and could not quite get it right. Here is a query that works and returns the expected results, however notice I have to pass name_last/birth_year/memNum twice. Is there a better way?

SELECT TOP 1 m.abn,
m.aliases,
m.birth_year,
m.user_stamp,

q.updatePending,
q.name_first,
q.name_last,
q.company,
q.address1,
q.mailing_address,
q.city,
q.state,
q.zipcode,
q.email_address

FROM (
SELECT TOP 1
1 AS updatePending,
a.entity_number,
a.name_first,
a.name_last,
NULLIF(LTRIM(RTRIM(
LTRIM(RTRIM(ISNULL(a.company, ''))) +
LTRIM(RTRIM(ISNULL(a.firm_name, ''))))),'') AS company,
a.address1,
a.mailing_address,
a.city,
a.state,
a.zip_code AS zipcode,
a.internet_address AS email_address,
a.time_stamp
FROM statebar.dbo.STAGING_Address_Change_Request a
INNER JOIN Member m ON m.entity_number = a.entity_number
WHERE a.entity_number = (
SELECT m.entity_number
FROM Member m
INNER JOIN Named_Entity ne ON (ne.entity_number = m.entity_number)
WHERE ne.name_last = 'jones'
AND m.birth_year = '1975'
AND m.memNum = '12345'
)
AND a.time_stamp > m.time_stamp

UNION ALL

SELECT TOP 1
0 AS updatePending,
ne.entity_number,
ne.name_first,
ne.name_last,
NULLIF(LTRIM(RTRIM(
LTRIM(RTRIM(ISNULL(ne.company, ''))) +
LTRIM(RTRIM(ISNULL(ne.firm_name, ''))))),'') AS company,
ne.address1,
ne.mailing_address,
ne.city,
ne.state,
ne.zip_code,
ne.internet_address AS email_address,
m.time_stamp
FROM Member m
INNER JOIN Named_Entity ne ON (ne.entity_number = m.entity_number)
LEFT JOIN statebar.dbo.STAGING_Address_Change_Request a ON a.entity_number = m.entity_number
WHERE ne.entity_number = (
SELECT m.entity_number
FROM Member m
INNER JOIN Named_Entity ne ON (ne.entity_number = m.entity_number)
WHERE ne.name_last = 'jones'
AND m.birth_year = '1975'
AND m.memNum = '12345'
)
AND m.time_stamp > a.time_stamp
ORDER BY updatePending DESC, a.time_stamp DESC) q
INNER JOIN Member m on m.entity_number = q.entity_number

ORDER BY q.time_stamp DESC

Answer

Here is a simple query that will help you return the most recent record:

--Only selects the top row with the most recent record
SELECT TOP 1 * FROM record
(
    --Select rows with the same ID
    SELECT name, address, city, state, zipcode, time_stamp
    FROM Member
    WHERE ID = 123
    UNION ALL
    SELECT name, address, city, state, zipcode, time_stamp
    FROM MemberUpdates
    WHERE ID = 123
) t
ORDER BY t.time_stamp DESC --Order the table by time_stamp to get the most recent record
-- DESC is used because datetime is ordered by oldest first in ascending order.