Question 114657 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Trying to include rows with a Full Outer Join & Comparing in the same table

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?

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search