CODEWITHSUNDEEP
phpsql
din
din

Reputation: 25

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

Upvotes: 1

Views: 180

Answers (3)

David Dierick
David Dierick

Reputation: 69

Indeed MINUS is not in mysql.

you query is fairly simple, but I would avoid a solution with NOT IN because it's not optimal at all if there's a lot of records in your payments table.

I would advise the use of NOT EXISTS

SELECT b.bookingid FROM bookings b 
WHERE checkindate >= '$new_date_in' 
  AND checkoutdate <= '$new_date_out' 
AND NOT EXISTS ( select 1 from payments p where b.bookingid = p.bookingid )

Upvotes: 1

Shushil Bohara
Shushil Bohara

Reputation: 5656

Try this as well:

SELECT b.bookingid
FROM bookings b
LEFT JOIN payments p ON p.bookingid = b.bookingid
WHERE 'yourdatevariable' BETWEEN b.checkindate AND b.checkoutdate  
    AND p.bookingid IS NULL

you can retrieve all the columns that you want and it can help you in the performance as well.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270191

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.

Upvotes: 1

Related Questions