Multiple conditions in SELECT Statement

Question

I am checking my database to see if there are any overlapping appointments. It works for ADDing appointments, but when I go to UPDATE my SQL counts the record I am updating as an overlapping appointment when I try to change it. Here is the SQL I am using in the UPDATE statement.

    "SELECT * FROM appointments " +
    "WHERE ('" + start + "' BETWEEN Start and End OR '" + end + "' 
     BETWEEN Start AND End OR '" + start + "' > Start AND '" + end + "' < End AND
     Appointment_ID <> " + id + ")";

This still pulls the record with the appointment id that I’m giving it. Anyone have any suggestions on how to exclude any appointments that have the id I’m updating but still meeting the other criteria.

sql

Hamster2k · Answer

currently, you are only checking for the id of the appointment to update when the third clause you have chained with ORs triggers because of the order of operations for OR and AND. Your query fetches a result when the start date is overlapping with the appointment OR your end date is is overlapping with the appointment OR it has a different ID and the dates are encapsulating the appointment.

Also, you shouldn't need to have brackets around your whole WHERE clause but you can use the opening bracket and move the closing bracket to make sure that the condition with the id is always evaluated like so:

    "SELECT * FROM appointments " +
    "WHERE ('" + start + "' BETWEEN Start and End OR '" + end + "' 
     BETWEEN Start AND End OR '" + start + "' > Start AND '" + end + "' < End) AND
     Appointment_ID <> " + id;

Multiple conditions in SELECT Statement

One Answer

Add your own answers!

Ask a Question