I have two tables, Person and Address, as shown below:

Person

Address

Using these tables, I'd like to create a View that has a row for each person in Person table but only the most recent address (i.e. max address_id) needs to be picked up as shown below (e.g., Jane's address should be set to '20 Main St' in the View):

Using below script, it seems like the tables are joining as intended but I don't see any value in the address field. I tried several different variations of the same script, but am pretty much spinning my wheels at this point.

Any tips will be much appreciated!

SELECT
  p.person_id AS id,
  p.first_name AS first_name,
  p.last_name AS last_name,
  a.address AS address
FROM Person p
LEFT JOIN Address a ON
  a.address_id = (
    SELECT MAX(address_id)
    FROM Address
    WHERE p.person_id = a.person_id
  )

<iframe name="sif1" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>

CodePudding user response：

i have tested this query and it meets your need.

select p.id, p.name, a.address
from person p
         left join address a 
             on p.id = a.person_id 
             and a.id = (select max(id) from address where person_id = p.id)
;