CODEWITHSUNDEEP
mysqlsql
Rober
Rober

Reputation: 6108

Mysql SQL Minus query

I want to check for one booking id = 2 and booking_state = PENDING, if there is another booking PENDING for same boat_id and same dates. My only problem is I want to exclude the booking_id = 2 in my final result.

SELECT distinct b1.id
FROM bookings b1, bookings b2, booking_states bs 
WHERE bs.name = 'Pending'
AND b1.id = 2
AND b1.id <> b2.id
AND bs.id = b1.booking_state_id
AND bs.id = b2.booking_state_id
AND b1.boat_id = b2.boat_id
AND (date(b1.departure_date) <= b2.departure_date AND date(b1.arrival_date) >= b2.departure_date)   
OR (date(b1.departure_date) <= b2.arrival_date AND date(b1.arrival_date) >= b2.arrival_date);

Upvotes: 0

Views: 86

Answers (2)

Rober
Rober

Reputation: 6108

I got it with the help of @spencer7593:

The key was to select b2.id instead of b1.id

This is the final query:

SELECT distinct b2.*
FROM bookings b1, bookings b2, booking_states bs 
WHERE bs.name = 'Pending'
AND b1.id = 2
AND b1.id <> b2.id
AND bs.id = b1.booking_state_id
AND bs.id = b2.booking_state_id
AND b1.boat_id = b2.boat_id
AND (date(b1.departure_date) <= b2.departure_date AND date(b1.arrival_date) >= b2.departure_date)   
OR (date(b1.departure_date) <= b2.arrival_date AND date(b1.arrival_date) >= b2.arrival_date)
AND NOT EXISTS (SELECT 1 
FROM bookings b 
WHERE b1.id <> b.id)

Upvotes: 0

spencer7593
spencer7593

Reputation: 108460

It's not clear what resultset you want returned.

The query is returning b1.id (as a column reference in the SELECT list), and the predicates in the query specify that b1.id = 2, so if the query returns any rows, it's going to be a value of 2.

Perhaps you meant to return b2.id, the id of the rows that "matched" id = 2.

Or, perhaps you meant to specify b2.id = 2 rather than b1.id = 2.

The way your question is written, it's impossible to know what resultset you are expecting.

Upvotes: 1

Related Questions