Selecting a Single Row from Joined Table

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

Person

Selecting a Single Row from Joined Table

Address

Selecting a Single Row from Joined Table

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):

Selecting a Single Row from Joined Table

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
  )

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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)
;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x