CODEWITHSUNDEEP
sqlpostgresql
SinGar
SinGar

Reputation: 111

Refining my SQL query to only show people who appear two or more times on a table

I am trying to write an SQL query that "Find bookings for a guest who has booked one room several times on different dates."

I have a postgress database with a guests table 

hotel7=# SELECT * FROM guest;
 guest_id |  name   |    phone     |     email     
----------+---------+--------------+---------------
    1 | kat     | 111-111-1111 | [email protected]
    2 | andy    | 222-222-2222 | [email protected]
    3 | theda   | 333-333-3333 | [email protected]
    4 | forrest | 444-444-4444 | [email protected]
    5 | trent   | 555-555-5555 | [email protected]
    6 | cyle    | 666-666-6666 | [email protected]
(6 rows)

and a reservation table that lists all the reservations that have been made.

hotel7=# SELECT * FROM reservation;
 res_id | guest_id |  check_in  | check_out  
--------+----------+------------+------------
  1 |        1 | 2017-12-01 | 2017-12-03
  2 |        1 | 2017-12-05 | 2017-12-07
  3 |        2 | 2017-12-01 | 2017-12-02
  4 |        2 | 2017-12-01 | 2017-12-10
  5 |        3 | 2017-12-01 | 2017-12-10
  6 |        4 | 2017-12-15 | 2017-12-30
  7 |        5 | 2017-12-15 | 2017-12-22
(7 rows)

There are a few other tables that don't seem necessary to the question.

So far the closest I have gotten is 

hotel7=# SELECT g.name FROM guest AS g  
LEFT JOIN reservation AS r
ON r.guest_id = g.guest_id;
  name   
---------
 kat
 kat
 andy
 andy
 theda
 forrest
 trent
 cyle
(8 rows)

All I need is to only show the people who appear twice on the list.

Upvotes: 0

Views: 625

Answers (2)

Vash
Vash

Reputation: 1787

You can try this: 

SELECT A.guest_id, A.name
FROM
guest A
LEFT JOIN
reservation B
ON A.guest_id = B.guest_id
GROUP BY A.guest_id, A.name
HAVING COUNT(res_id) > 1;

For every guest_id, it will calculate how many (non-NULL)res_id are present in the table reservation and with HAVING condition, you can make sure that you get only those with more than one reservations.

If there were a field like booking_date, you could have used HAVING COUNT(DISTINCT booking_date) > 1 in the query to see if the booking was made on two different dates by the same person, but you only have check-in and check-out dates.

Upvotes: 1

S.H
S.H

Reputation: 1

Please try this

SELECT g.name FROM guest AS g
LEFT JOIN reservation AS r ON r.guest_id = g.guest_id having COUNT(guest_id)>1;

Upvotes: 0

Related Questions