Mysql error not following where condition

Question

Why is this sql giving tables with minimum field having null. Also when A has no data between the given date ranges, it is providing the table with all rooms having minimum as null

   SELECT `rooms`.*,A.`minimum`
    FROM (
        SELECT `room_id`, min(`available_rooms`) AS `minimum`
        FROM `room_bookings` 
        WHERE `date` BETWEEN '2014-02-01' and '2014-02-10' 
        GROUP BY `room_id`) as A
    INNER JOIN `rooms` on `rooms`.`room_id`=A.`room_id` 
    WHERE `rooms`.`location`='kathmandu' 
    AND `rooms`.`status`=1 
    AND A.`minimum`!=NULL

replace `AND A.minimum !=NULL` with `AND A.minimum is not NULL`. null checks need to use the `IS` operator — juergen d, Jan 13 '14 at 11:52

score 2 · Answer 1 · answered Jan 13 '14 at 11:56

 SELECT `rooms`.*,A.`minimum`
    FROM (
        SELECT `room_id`, min(`available_rooms`) AS `minimum`
        FROM `room_bookings` 
        WHERE `date` BETWEEN '2014-02-01' and '2014-02-10' 
        GROUP BY `room_id` having minimum > 0) as A
    INNER JOIN `rooms` on `rooms`.`room_id`=A.`room_id` 
    WHERE `rooms`.`location`='kathmandu' 
    AND `rooms`.`status`=1

score 2 · Answer 2 · answered Jan 13 '14 at 11:57

SELECT `rooms`.*,A.`minimum`
FROM (
    SELECT `room_id`, min(`available_rooms`) AS `minimum`
    FROM `room_bookings` 
    WHERE `date` BETWEEN '2014-02-01' and '2014-02-10' 
    GROUP BY `room_id`) as A
INNER JOIN `rooms` on `rooms`.`room_id`=A.`room_id` 
WHERE `rooms`.`location`='kathmandu' 
AND `rooms`.`status`=1 
AND A.`minimum` is not NULL

This would be the correct syntax ( change != to is not )

score 1 · Answer 3 · answered Jan 13 '14 at 11:57

try this with IS NOT NULL

SELECT `rooms`.*,A.`minimum`
    FROM (
        SELECT `room_id`, min(`available_rooms`) AS `minimum`
        FROM `room_bookings` 
        WHERE `date` BETWEEN '2014-02-01' and '2014-02-10' 
        GROUP BY `room_id`) as A
    INNER JOIN `rooms` on `rooms`.`room_id`=A.`room_id` 
    WHERE `rooms`.`location`='kathmandu' 
    AND `rooms`.`status`=1 
    AND A.`minimum` IS NOT NULL

score 1 · Accepted Answer · answered Jan 13 '14 at 12:24

1

Try this:

SELECT r.*, MIN(rb.available_rooms) minimum 
FROM rooms r 
INNER JOIN room_bookings rb ON r.room_id = rb.room_id AND rb.date BETWEEN '2014-02-01' AND '2014-02-10' 
WHERE r.location = 'kathmandu' AND r.status = 1 
GROUP BY r.room_id HAVING minimum IS NOT NULL

answered Jan 13 '14 at 12:24

Saharsh Shah

28,687
8
48
83

1

also is there any way where we can put `r.total_rooms` to the `minimum` when `minimum` is null?? – Bishal Paudel Jan 14 '14 at 04:09

Mysql error not following where condition

4 Answers4