I have a couple of tables XX_PERS and XX_ADDRESS as given below. I would like to pick the most recent "city" for every person on the table xx_pers. If no corresponding record in xx_address, it should still the person id, name with city as null.
CREATE TABLE xx_pers
(ID number primary key,
name varchar2(100)
);
CREATE TABLE xx_address
(
addr_id NUMBER PRIMARY KEY,
pers_id NUMBER ,
city VARCHAR2(25),
FOREIGN KEY(pers_id) REFERENCES xx_pers(id)
);
insert into xx_pers values (1,'Messi');
insert into xx_pers values (2,'Muller');
insert into xx_pers values (3,'Ronaldo');
insert into xx_address values (100,1,'Argentina');
insert into xx_address values (102,1,'Madrid');
insert into xx_address values (101,2,'Germany');
insert into xx_address values (103,2,'Munich - DE');
The below query returns the most recent "city" for person 1 and 2 but not sure how to get the 3rd person.
select ID, name, city
from xx_pers xx
, xx_address y
Where xx.id = y.pers_id
and y.addr_id in (select max(addr_id) from xx_address group by pers_id);