SQL Access Select NOT IN from two tables

Question

Hello i am quite new to using SQL statements and my SQL statement does not return the proper result. I have two tables Room (contains Room details) and Booking(contains ID of Room booked). The RoomID of Booking should be retrieved which is NOT IN the range of Arrival Date AND Departure Date & has RoomType = single. Finally the ID is searched from the Room table to display the details.

psmt = con.prepareStatement("SELECT RmID,RmName,RmType,RmDescription,RmRate,RmMaxPerson FROM Room WHERE RmID NOT IN (SELECT RmID FROM Booking WHERE ((BookArrivalDate >= ? AND BookArrivalDate < ?))) AND RmType = ?");
psmt.setDate(1, sqlDateAStart);
psmt.setDate(2, sqlDateAEnd);
psmt.setString(3, RoomType);
rs = psmt.executeQuery()

Thank you

This question is currently tagged for both [mysql] and [ms-access-2007]. Which database are you actually using? — Gord Thompson, Oct 21 '13 at 17:27
Thank you so much. It works now. I ignored this fact. I got the logic right but it was not the correct way. u r a lifesaver ;) — Ishildur Baggins, Oct 22 '13 at 12:17

score 0 · Answer 1 · answered Oct 21 '13 at 12:11

0

You have an error where you are not considering the departureDate Try this::

SELECT 
RmID,
RmName,
RmType,
RmDescription,
RmRate,RmMaxPerson 
FROM 
Room r
INNER JOIN Booking b on (r.RmID =b.RmID ) 
WHERE BookArrivalDate < ? AND BookDepartureDate > ? AND RmType = ?

answered Oct 21 '13 at 12:11

Sashi Kant

13,277
9
44
71

"SELECT RmID,RmName,RmType,RmDescription,RmRate,RmMaxPerson FROM Room r INNER JOIN Booking b ON (r.RmID = b.RmID) WHERE (BookArrivalDate < ? AND BookDepartureDate > ? AND RmType = ?)" – Ishildur Baggins Oct 21 '13 at 12:30
I am not getting the hang of it. – Ishildur Baggins Oct 21 '13 at 12:31
Yes thnks, i have replaced the BookDepartureDate but still nothing – Ishildur Baggins Oct 21 '13 at 12:40

Ajeesh · Answer 2 · 2013-10-22T04:25:33.597

You have to change the below code to select values from both the tables and you are dealing with a booking system which will require both date & time so I recommend you to use Timestamp as a datatype for both BookArrivalDate and BookDepartureDate. I have posted an sql query which will check whether the given FromDate & ToDate is present between any of the Booked Date and return results if there's any.

SELECT RmID,RmName,RmType,RmDescription,RmRate,RmMaxPerson
FROM Room,Booking
WHERE 
(RmType ='single') 
AND 
((
('fromtime'>=arrivaltime)
AND 
('totime'<=departuretime))
OR 
(
('fromtime'<=arrivaltime)
 AND 
 ('totime'>=departuretime))
OR 
(
('fromtime'<=arrivaltime) 
AND 
('totime'>=arrivaltime)) 
OR 
(
('totime'>=arrivaltime)
 AND
  ('totime'<=departuretime)) 
OR 
(
('totime'>=departuretime)
 AND
  ('fromtime'<=departuretime))
OR 
(
('fromtime'>=arrivaltime)
 AND
  ('fromtime'<=departuretime)
))

Here arrivaltime is your BookArrivalDate which is present in database and departuretime is your BookDepartureDate which is present in database. fromtime & totime are the values which you give in prepared statement/date ranges.

Thanks, i appreciate but i did tested the dates which indeed requires both date & time and Dates passed to the sql string seems good. I 'feel' the problem is with the SQL statement itself (improper use of inner join or RoomID or something else) — Ishildur Baggins, Oct 21 '13 at 13:03

score 0 · Accepted Answer · answered Oct 21 '13 at 17:18

In cases like this it can be very helpful to isolate a particular piece of the application and use a little test program to experiment with it. For example, I created a [Booking] table in Access...

BookingID  RmID  BookArrivalDate  BookDepartureDate
---------  ----  ---------------  -----------------
        1   101  2013-10-21       2013-10-23       
        2   102  2013-11-01       2013-11-03

...and then used the following little test program to run queries against it:

import java.sql.*;
import org.joda.time.*;

public class JDBCQuery {
    public static void main(String args[]) {
        Connection conn = null;
        PreparedStatement s = null;
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection(
                    "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + 
                    "DBQ=C:\\Users\\Public\\Database1.accdb;");

            LocalDate requestedArrivalDate = LocalDate.parse("2013-10-12");
            LocalDate requestedDepartureDate = LocalDate.parse("2013-10-21");

            s = conn.prepareStatement(
                    "SELECT BookingID, RmID FROM Booking WHERE BookDepartureDate > ? AND BookArrivalDate < ?");
            s.setDate(1, new java.sql.Date(requestedArrivalDate.toDate().getTime()));
            s.setDate(2, new java.sql.Date(requestedDepartureDate.toDate().getTime()));
            ResultSet rs = s.executeQuery();

            System.out.println(String.format("Requested arrival: %s", requestedArrivalDate.toString()));
            System.out.println(String.format("Requested departure: %s", requestedDepartureDate.toString()));
            System.out.println("The following bookings conflict with that request...");
            if (rs!=null) {
                while (rs.next()) {
                    System.out.println(String.format("BookingID: %d, RmId: %d", rs.getInt("BookingID"), rs.getInt("RmID")) );
                }
            }
            System.out.println("(end of list)");

        } catch( Exception e ) {
            e.printStackTrace();
        } finally {
            try {
                if (s != null) {
                    s.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch( Exception e ) {
                e.printStackTrace();
            }
        }
    }
}

Once I was convinced that I got the SQL logic right and was able to isolate the conflicting Bookings (and hence the RmID values that I could not use) then it became clear that I wanted my final query to look something like this:

SQL = 
        "SELECT RmID FROM Room " +
        "WHERE " + 
            "RmID NOT IN " +
                "(" +
                    "SELECT RmID FROM Booking " +
                    "WHERE BookDepartureDate > ? AND BookArrivalDate < ? " +
                ")" +
            "AND RmType = ?";
s = conn.prepareStatement(SQL);
s.setDate(1, new java.sql.Date(requestedArrivalDate.toDate().getTime()));
s.setDate(2, new java.sql.Date(requestedDepartureDate.toDate().getTime()));
s.setString(3, "Single");
ResultSet rs = s.executeQuery();

SQL Access Select NOT IN from two tables

3 Answers3