din din - 1 year ago 54
PHP Question

How to use MINUS operator in SQL?

I want to retrieve all the details of bookings table which didn’t make the payment. I have used MINUS operator. But it didn’t work. It gives SQL Error. How to get bookingid which doesn’t exist in payments table.

This is my code.

$SQL ="SELECT bookingid FROM bookings WHERE checkindate >= '$new_date_in' AND checkoutdate <= '$new_date_out' MINUS SELECT bookingid FROM payments ";
$run=mysql_query($SQL,$con) or die ("SQL error");

----$new_date_in and $new_date_out are user selected start and end dates---

Bookings Table

payments table

Answer Source

Just use not exists or not in:

SELECT b.bookingid
FROM bookings b
WHERE b.checkindate >= '$new_date_in' AND
      b.checkoutdate <= '$new_date_out' AND
      b.bookingid NOT IN (SELECT bookingid FROM payments);

This also gives you the opportunity to get other fields from bookings.

Also, you should use parameters in the query rather than stuffing them in the query string.