din din - 1 year ago 66
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download