CODEWITHSUNDEEP
phpmysql
Farhan
Farhan

Reputation: 1483

Mysql select record that should have today and tomorrow date

I want to select record that must have two or more entries but should have today and tomorrow date in table. I am saving date in table in date format.

SELECT `availibility`.* 
FROM (`availibility`) 
WHERE `property_id`= 8818 
AND (availibility.unavailibility_date between CURDATE() 
AND DATE_ADD(CURDATE(),INTERVAL 1 DAY))

I am using above query but this will true even one date (today or tomorrow) exists. I want to get such record that should have both dates for example

+---------+----------------+------------+
|   ID    |   property_id  |  Date      |
+---------+----------------+------------+
| 369516  | 8818           | 2013-01-19 |
| 369517  | 8818           | 2013-01-18 |
| 369518  | 8818           | 2013-01-17 |
| 418021  | 8818           | 2013-08-27 |
| 418022  | 8818           | 2013-08-28 |
| 418022  | 8818           | 2013-08-29 |
| 418022  | 2001           | 2013-07-29 |
| 418022  | 2001           |2013-07-30  |
+---------+----------------+------------+

8818 property should come in record set because both date exists here

Upvotes: 8

Views: 16728

Answers (4)

Erik Baan
Erik Baan

Reputation: 436

Use a JOIN to make sure the second record exists:

SELECT `availibility`.* 
FROM `availibility`
JOIN `availibility` AS availibility_tomorrow ON availibility_tomorrow.property_id =     availibility.property_id AND availibility_tomorrow.unavailibility_date = DATE_ADD(CURDATE    (),INTERVAL 1 DAY))
WHERE `property_id`= 8818 AND availibility.unavailibility_date = CURDATE()

Upvotes: 1

chirag ode
chirag ode

Reputation: 960

You can use:

NOW() + INTERVAL 1 DAY

If you are only interested in the date, not the date and time then you can use CURDATE instead of NOW:

CURDATE() + INTERVAL 1 DAY

your query should be

SELECT `availibility`.* 
FROM (`availibility`) 
WHERE `property_id`= 8818 
AND (availibility.unavailibility_date between CURDATE() 
AND CURDATE() + INTERVAL 1 DAY // change here

Upvotes: 5

jaczes
jaczes

Reputation: 1404

If i have understood this good...

SELECT `property_id`, count(*) as no
FROM (`availibility`) 
WHERE `property_id`= 8818 
AND (availibility.unavailibility_date=CURDATE() 
OR availibility.unavailibility_date=DATE_ADD(CURDATE(),INTERVAL 1 DAY))
having no =2

Upvotes: 0

fthiella
fthiella

Reputation: 49089

SELECT property_id
FROM yourtable
WHERE date IN (CURDATE(), CURDATE() + INTERVAL 1 DAY)
GROUP BY property_id
HAVING COUNT(DISTINCT date)=2

Please see fiddle here.

Upvotes: 11

Related Questions