Clash Royale CLAN TAG#URR8PPP

SQL selection based on a particular field value

I have a table of people and a table of their addresses. A person can have multiple addresses, but there is an effective date attached to each address.

I want to link people and address to their greatest effective date and I am stuck, with my query attached below I am getting just the maximum effective date in the entire table. Please note that this is RPG so the dates are stored like numbers, for instance today would be 20,180,831 (2018-08-31).

SELECT PERSON.ID, PERSON.NAME, ADDRESS.ID, ADDRESS.ADD1, ADDRESS.ADD2, ADDRESS.CITY FROM PERSON
LEFT JOIN
(
SELECT * FROM ADDRESS
WHERE EFF_DATE IN (SELECT MAX(EFF_DATE) FROM ADDRESS)
) AS A
ON PERSON.ID = A.ID


I know the problem is in the WHERE clause but I'm drawing a blank.

1 Answer
1

You need to use a LEFT OUTER JOIN between the table person and the table address, adding that the address must match the last eff_date.

LEFT OUTER JOIN

person

address

You didn't say what's the database but considering you tagged it RPG, I assumed DB2:

with d as (
select person_id, max(eff_date) as max_date
from address
group by person_id
)
select p.*
from person p
left join address a on p.id = a.person_id
and (a.person_id, a.eff_date) in (
select person_id, max_date from d
)


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.