CODEWITHSUNDEEP
mysql
Min Ko Ko
Min Ko Ko

Reputation: 141

how to find the all column value between start and end date? excepted output

I run the query 

SELECT * FROM roomcalendar where day between '2016-08-25' and '2016-08-28'

the result show in the following image.

image

but i want to find the value 1 of all avaroom between '2016-08-25' and '2016-08-28'

if i use like this 

SELECT * FROM roomcalendar where day between '2016-08-25' and '2016-08-28' and NOT avaroom=0

it shows 1 row

i need to check all the avaroom = 0 beteewn start and end date.

How to do it?

Upvotes: 0

Views: 61

Answers (1)

sagi
sagi

Reputation: 40481

The question is what do you want as a result? 

SELECT 'YES' as Column_Name
FROM roomcalendar
WHERE day between '2016-08-25' and '2016-08-28'
  AND avaroom <> 0

This will return YES if there are any records with avaroom <> 0 , and NULL if not.

If you want to select all but only if no avaroom <> 0 exists, then :

SELECT * FROM roomcalendar t
WHERE NOT EXISTS(SELECT 1 FROM roomcalendar s
                 WHERE s.day between '2016-08-25' and '2016-08-28'
                   AND s.avaroom <> 0)
 AND t.day between '2016-08-25' and '2016-08-28'

Upvotes: 1

Related Questions