CODEWITHSUNDEEP
mysqlsqlexistspredicate
marcq
marcq

Reputation: 483

SQL OR NOT EXISTS doesn't work

I have a question concerning the following query which doesn't work :

{thistable}.id IN (
SELECT id
FROM fab_booking_slots AS slots 
WHERE id = '{fab_booking___book_starttime_raw}'
OR NOT EXISTS (
SELECT id +1 FROM (
    SELECT taken.book_date, slots.*, count(taken.book_date) AS nbre 
    FROM fab_booking_taken AS taken 
    CROSS JOIN fab_booking_slots AS slots 
    WHERE NOT ((slots.heuredepart_resa < taken.book_end 
               AND slots.heurearrivee_resa > taken.book_start)) 
    AND DATE(taken.book_date) = DATE('{fab_booking___book_bookingdate}') 
    GROUP BY slots.id) AS x 
WHERE nbre = (
    SELECT count(taken.book_date) 
    FROM fab_booking_taken AS taken 
    WHERE taken.book_date = x.book_date)
    )
)
Order BY id ASC

This query should return the value from the dropdown already existing into the database and if they are available slots, the available slots.

When I'm running the 2 SELECT independently they are working.

Would appreciate some expertise here, since I can't find the reason and I'm stuck.

This thread follows the following thread :

STACKOVERFLOW

Tables can be downloaded there.

Thank you in advance for your support.

Cheers,

Marc

Upvotes: 1

Views: 607

Answers (1)

mendosi
mendosi

Reputation: 2051

In this case I think the error is in the way that you have used WHERE NOT EXISTS. This will return true if your subquery returns no rows. Normally the subquery that you use with EXISTS or NOT EXISTS would be a correlated subquery which references a column from the outer select (in your case fab_booking_slots) so that the subquery returns a different result for each row of the outer select.

So, enough of the theory which you probably already know (let's say it's for future readers), what you did here is you didn't correlate the subquery with an outer reference. This is complicated by the fact that you have used the same table alias, slots in both the outer query and the subquery. I would assign different aliases to prevent confusion.

I have simplified the logic in your subquery a bit, this should still return correct results. Note that there is a trap with the times that go after midnight so we need to handle them by adding a day to those times and then the comparisons will work correctly:

{thistable}.id IN (
SELECT t.id
FROM fab_booking_slots AS t 
WHERE t.id = null
OR NOT EXISTS (
    Select 1 
    From  (Select book_date, 
           Convert(book_start, datetime) As book_start, 
           Case When book_end < '02:00:00' Then Date_Add(Convert(book_end, datetime), INTERVAL 1 DAY) Else Convert(book_end, datetime) End As book_end 
           From fab_booking_taken) AS p1
    Where Date(p1.book_date) = '2017-01-10'
    And Convert(p1.book_end, datetime) > Convert(t.heuredepart_resa, datetime)
    And Convert(p1.book_start, datetime) < Case When t.heurearrivee_resa < '02:00:00' Then Date_Add(Convert(t.heurearrivee_resa, datetime), INTERVAL 1 DAY) Else Convert(t.heurearrivee_resa, datetime) End
)
)
Order BY id ASC;

Upvotes: 2

Related Questions