Finding available rooms SQL

See more:

Java

MySQL

I want to find available rooms in a certain hotel, however, I am not able to find my mistake in my SQL queries (below). I hope someone can help me find my mistake.

My Booking Entity looks like this

@Entity
@Table(name = "bookings")
public class BookingEntity extends BaseEntity {

    @ManyToOne
    private UserEntity guest;

    @ManyToOne
    private AccommodationEntity property;

    @Column(columnDefinition = "DATE", nullable = false)
    private LocalDate checkIn;

    @Column(columnDefinition = "DATE", nullable = false)
    private LocalDate checkOut;

Booked Rooms entity

@Entity
@Table(name = "booked_rooms")
public class BookedRoomsEntity extends BaseEntity {

    @ManyToOne
    private BookingEntity booking;

    @ManyToOne
    private RoomEntity room;

Room Entity

@Entity
@Table(name = "rooms")
public class RoomEntity extends BaseEntity {

    @ManyToOne
    private AccommodationEntity accommodationEntity;
     private String roomType;

What I have tried:

I have 2 bookings in the DB -> FROM 2022-02-03 TO 2022-02-05 and 2022-01-28 TO 2022-01-29 both property_id = 1, room_id respectively 1 and 2 and this query instead of returning nothing, returns

2022-01-28 TO 2022-01-29

SELECT *
FROM bookings as b
        JOIN booked_rooms br on b.id = br.booking_id
WHERE b.property_id = 2
    AND br.room_id = 3
    AND b.check_in >= '2022-01-28' AND b.check_in < '2022-01-30'
   OR b.check_out > '2022-01-28' AND b.check_out < '2022-01-30'
   OR b.check_in < '2022-01-28' AND b.check_out > '2022-01-30'

I also tried like that expecting that it would return 1 booking but it does not return anything.

SELECT *
FROM booked_rooms as br
         JOIN bookings b on br.booking_id = b.id
WHERE b.property_id = 1
  AND br.room_id = 1
  AND '2022-01-28' > b.check_out
  AND '2022-01-29' < b.check_in