title: SQL too complicated nested query - can you make it simpler?
body: <p>I have built a hotel booking database in SQL Server. Three of the tables are below:</p>
  
  <p><a href="https://i.stack.imgur.com/1mQo1.jpg" rel="nofollow noreferrer">E-model</a></p>
  
  <p>The relevant columns in those tables are:</p>
  
  <ul>
  <li><code>Booking</code>: <code>BookingID</code> (PK), <code>CheckInDate</code>, <code>CheckoutDate</code></li>
  <li><code>RoomBooking</code>: <code>RoomBookingID</code> (PK), <code>FK_Booking_ID</code> (FK), <code>FK_RoomID</code> (FK)</li>
  <li><code>Room</code>: <code>RoomID</code> (PK)</li>
  </ul>
  
  <p>Now I wrote a SQL query (shown below) to try to figure out what rooms are free on a certain date (2017-10-02 to 2017-10-04). The code solves the problem but gets very complicated. I know how to solve this easier by having the <code>CheckInDate</code> and <code>CheckOutDate</code> in the table <code>RoomBooking</code>, but I want this set-up to work. Do you have any ideas how to make the code below simple?</p>
  
  <p>BR/ Mike</p>
  
  <pre><code>SELECT FK_RoomID 
  FROM RoomBooking
  WHERE FK_RoomID IN 
        (SELECT FK_RoomID 
         FROM RoomBooking
         WHERE FK_BookingID NOT IN    
               (SELECT BookingID 
                FROM Booking
                WHERE CheckInDate &lt;= '2017-10-04'
                  AND CheckOutDatum &gt; '2017-10-02')   
               )
           AND FK_RoomID NOT IN 
               (SELECT FK_RoomID 
                FROM RoomBooking
                WHERE FK_BookingID IN     
                      (SELECT FK_BookingID 
                       FROM RoomBooking
                       WHERE FK_RoomID IN     
                             (SELECT FK_RoomID  
                              FROM RoomBooking
                              WHERE FK_BookingID IN   
                                    (SELECT FK_BookingID 
                                     FROM RoomBooking
                                     WHERE FK_BookingID NOT IN 
                                           (SELECT BookingID 
                                            FROM Booking
                                            WHERE CheckInDate &lt;= '2017-10-04'
                                              AND CheckOutDate &gt; '2017-10-02')
                                           )
                                     )
                              )
                          AND FK_BookingID NOT IN 
                              (SELECT FK_BookingID 
                               FROM RoomBooking
                               WHERE FK_BookingID NOT IN  
                                     (SELECT BookingID 
                                      FROM Booking
                                      WHERE CheckInDate &lt;= '2017-10-04'
                                        AND CheckOutDate &gt; '2017-10-02')
                                     )
                              ); 
  </code></pre>