Trying to include rows with a Full Outer Join & Comparing in the same table-CodePudding

I'm attempting to return the First person to check in in each Room_id by joining the PERSON and CHECK_IN tables http://sqlfiddle.com/#!17/35d930 > Select PostGreSQL 9.6 > Build Schema > Paste Query

CREATE TABLE person
    ("id" int)
;
    
INSERT INTO person
    ("id")
VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (6)
;

CREATE TABLE check_in
    ("id" int, "person_id" int, "room_id" int, "check_in_date" timestamp, "check_out_date" timestamp)
;
    
INSERT INTO check_in
    ("id", "person_id", "room_id", "check_in_date", "check_out_date")
VALUES
    (100, 1, 202, '2020-10-01 00:00:00', '2021-09-05 00:00:00'),
    (101, 2, 201, '2020-12-15 00:00:00', '2021-02-15 00:00:00'),
    (104, 3, 204, '2021-05-20 00:00:00', '2021-07-04 00:00:00'),
    (106, 4, 202, '2022-08-01 00:00:00', NULL),
    (108, 3, 204, '2021-08-15 00:00:00', NULL)
;


select c1.person_id, c1.room_id, c1.check_in_date
from check_in c1
FULL OUTER JOIN check_in c2 on c2.room_id = c1.room_id
where c1.check_in_date < c2.check_in_date 
order by c1.room_id

I'm returning room_ids 202 and 204, but cannot get the select to return 201.. Should I not be using a full outer join?

CodePudding user response：

We don't need to join the person table as we have all the info we need in the check_in table.

select   id 
        ,person_id  
        ,room_id    
        ,check_in_date  
        ,check_out_date
from    (
         select   *
                  ,row_number() over(partition by room_id order by check_in_date desc) as rn
         from     check_in
        ) t
where   rn = 1

id	person_id	room_id	check_in_date	check_out_date
101	2	201	2020-12-15 00:00:00	2021-02-15 00:00:00
106	4	202	2022-08-01 00:00:00	null
108	3	204	2021-08-15 00:00:00	null

Fiddle

CodePudding user response：

Your where condition transforms the outer join into an inner one. See the answer with window function above as well

select c1.person_id, c1.room_id, c1.check_in_date
from check_in c1
where c1.check_in_date = (select min(c2.check_in_date) from check_in c2 
  where c2.room_id = c1.room_id ) 
order by c1.room_id

CodePudding user response：

select c1.person_id, c1.room_id, c1.check_in_date from check_in as c1 where c1.check_in_date in (select min(check_in_date) from check_in as c2 join person as p on p.id = c2.person_id group by c2.room_id) group by 2,1,3 order by c1.room_id