Ytukyg

I have a DB Fiddle as an example: https://www.db-fiddle.com/f/vX7yFVk8j6FPTSm2QVoPNE/2

I'm trying to figure out how to find all bookings for a guest who has booked one room several times on different dates. I can't use a static value/variable like bookings.check_in_date = '2018-03-05', bookings.guest_id = 001 or use subqueries.

I tried:

SELECT bookings.guest_id, bookings.room_number, bookings.check_in_date, bookings.check_out_date

FROM bookings

GROUP BY bookings.guest_id, bookings.room_number, bookings.check_in_date, bookings.check_out_date

HAVING count(distinct bookings.check_in_date) >= 2;

However, it isn't giving me the result I'm looking for but rather I'm looking for this as a result:

guest_id, room_number, check_in_date, check_out_date

1, 203, 2018-03-01T00:00:00.000Z, 2018-03-07T00:00:00.000Z

1, 203, 2018-03-14T00:00:00.000Z, 2018-03-21T00:00:00.000Z

Schema (PostgreSQL v10.0)

CREATE TABLE guests (

  id integer,

  name text,

  address text,

  city text,

  state text,

  postal_code integer,

  phone_number numeric(10)

);



CREATE TABLE rooms (

  room_number integer,

  floor_number integer,

  view boolean,

  bed_type text

);



CREATE TABLE bookings (

  id integer,

  guest_id integer,

  room_number integer,

  order_date date,

  check_in_date date,

  check_out_date date

);



INSERT INTO guests (id, name, address, city, state, postal_code, phone_number)

VALUES

(1, 'John', '5 8th Avenue', 'New York', 'New York', 10001, 2021110190),

(2, 'Jane', '13 Wall St', 'New York', 'New York', 10001, 1112020190),

(3, 'Jim', '12 Water St', 'New York', 'New York', 10001, 3332224444),

(4, 'Fang', '12 Broadway', 'New York', 'New York', 10001, 1234567890),

(5, 'Bruce', '123 1st Ave', 'New York', 'New York', 10001, 9871112222);



INSERT INTO rooms (room_number, floor_number, view, bed_type)

VALUES

(101, 1, FALSE, 'King'),

(102, 1, FALSE, 'Queens'),

(203, 2, FALSE, 'Queens'),

(204, 2, TRUE, 'King'),

(409, 4, TRUE, 'King');



INSERT INTO bookings (id, guest_id, room_number, order_date, check_in_date, check_out_date)

VALUES

(18001, 001, 203, '2018-03-05', '2018-03-01', '2018-03-07'),

(18002, 001, 203, '2018-03-05', '2018-03-14', '2018-03-21'),

(18003, 001, 409, '2018-03-05', '2018-11-27', '2018-11-30'),

(18004, 001, 204, '2018-03-05', '2018-11-27', '2018-11-30'),



(18007, 002, 409, '2018-05-05', '2018-05-05', '2018-05-12'),

(18002, 002, 203, '2018-03-05', '2018-03-14', '2018-03-21'),

(18006, 002, 409, '2018-06-05', '2018-07-04', '2018-07-07'),



(18005, 003, 409, '2018-06-05', '2018-08-05', '2018-08-12'),



(18008, 004, 409, '2018-09-05', '2018-09-05', '2018-09-12');

Just counting the number of bookings per guest_id should get your the guests who have more than one booking

select a.*

from bookings a

join (

       select guest_id,room_number,count(*)

         from bookings

      group by guest_id,room_number

        having count(*)>1

      )b

  on a.guest_id=b.guest_id

And a.room_ number=b.room_number