BETWEEN Query Not Working

Question

I have a query which uses BETWEEN for showing the records between two dates. My query needs to show records whose arrival_date and departure_date between specific dates. But query somehow shows all records.

Column types are DATE.

SELECT DISTINCT art.* FROM accommodation_room_types art 
INNER JOIN accommodation_rooms ar ON art.id = ar.room_type
INNER JOIN accommodation a ON art.accommodation = a.id
WHERE a.id = 13 AND NOT EXISTS
(
    SELECT 1 FROM booked_rooms br INNER JOIN booking b ON br.booking = b.id
    WHERE br.room = ar.id
    AND
    (
        b.arrival_date BETWEEN '2017-12-16' AND '2018-04-16'
    ) 
    OR
    (
        b.departure_date BETWEEN '2017-12-16' AND '2018-04-16'
    )
)

Even I write BETWEEN 'asd' AND 'asd', it still shows all records and doesn't give any format error. Is my query wrong for showing records between two specific dates?

What you need is more parenthesis `WHERE X AND ( (B) OR (C) )` — Ibu, Apr 05 '17 at 21:53

Gordon Linoff · Answer 1 · 2017-04-06T01:57:06.437

0

I don't know if your logic is right or wrong, but your syntax is not doing what you intend. I would suggest:

WHERE a.id = 13 AND
     NOT EXISTS (SELECT 1
                 FROM booked_rooms br INNER JOIN
                      booking b
                      ON br.booking = b.id 
                 WHERE br.room = ar.id AND
                       (b.arrival_date BETWEEN '2017-12-16' AND '2018-04-16' OR  
                        b.departure_date BETWEEN '2017-12-16' AND '2018-04-16'
                       )    
                )

It strikes me that all that empty space in the query makes it hard to see that your logic was written as: A AND B OR C. Your intention (presumably) is A AND (B OR C).

edited Apr 06 '17 at 01:57

answered Apr 05 '17 at 21:49

Gordon Linoff

1,242,037
58
646
786

I am getting an error with yours: Unknown column `ar.id` in `on clause` – Malena T Apr 05 '17 at 21:56
@MalenaT . . . I misread that condition. It is just a correlation condition. You just need parentheses. – Gordon Linoff Apr 06 '17 at 01:57

score -1 · Answer 2 · answered Apr 05 '17 at 21:53

-1

Substitute 1 for *

The way you wrote query, it always return 1, regardless of conditions. Moreover, that is totally legit.

answered Apr 05 '17 at 21:53

Yuri G

1,206
1
9
13

The OP is returning `1` just to return some sort of data. Your fix is not doing what you think it does. – Ibu Apr 05 '17 at 21:53
If a query is being used in `EXISTS`, it doesn't matter what columns it returns -- `EXISTS` just tests whether any rows matched the conditions. – Barmar Apr 05 '17 at 22:29
you both right, I was wrong, my bad – Yuri G Apr 05 '17 at 22:34

BETWEEN Query Not Working

2 Answers2