CODEWITHSUNDEEP
phpmysqldateselectjoin
Scott Alan Kline
Scott Alan Kline

Reputation: 1

Mysql date range query returning wrong set of results

Here is the query:

SELECT `mainevents`.`maineventid`   AS `MainEventID`, 
       `mainevents`.`maineventcode` AS `MainEventCode`, 
       `mainevents`.`datestart`     AS `DateStart`, 
       `mainevents`.`dateend`       AS `DateEnd`, 
       `mainevents`.`location`      AS `Location`, 
       `mainevents`.`name`          AS `Name` 
FROM   `mainevents`, 
       `mainevents_lookup` 
WHERE  `mainevents`.`maineventid` = `mainevents_lookup`.`maineventid` 
       AND `mainevents`.`categoryid` = 1 
       AND ( ( `mainevents_lookup`.`datestart` >= '2016-02-27 00:00:00' 
               AND `mainevents_lookup`.`datestart` <= '2016-05-27 11:59:59' ) 
              OR ( `mainevents_lookup`.`dateend` >= '2016-02-27 00:00:00' 
                   AND `mainevents_lookup`.`dateend` <= '2016-05-27 11:59:59' ) 
              OR ( `mainevents_lookup`.`dateend` <= '2016-05-27 11:59:59' 
                   AND `mainevents_lookup`.`datestart` >= '2016-02-27 00:00:00' 
                 ) ) 
LIMIT  0, 30

It is trying to find the listings that have dates that fall within the selected two date ranges. DateStart is '2016-02-27 00:00:00' and DateEnd is '2016-05-27 11:59:59'

For some reason it keeps giving me the opposite results of what I want to find. I know the answer is right in front of me, anyone can give me a hand?

Upvotes: 0

Views: 45

Answers (3)

Scott Alan Kline
Scott Alan Kline

Reputation: 1

I should have done the default and validated the initial data. The data in the table was incorrect, the query was correctly pulling the future event as the date range in the lookup table was within the queried date ranges.

Upvotes: 0

Dave
Dave

Reputation: 5191

The 2nd OR condition should not be needed and it looks like the last AND test is using the wrong column (datestart instead of dateend).

AND ( ( `mainevents_lookup`.`datestart` >= '2016-02-27 00:00:00' 
           AND `mainevents_lookup`.`datestart` <= '2016-05-27 11:59:59' ) 
          OR ( `mainevents_lookup`.`dateend` >= '2016-02-27 00:00:00' 
               AND `mainevents_lookup`.`dateend` <= '2016-05-27 11:59:59' ) 
)

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

Check Overlaping date ranges (StartA <= EndB) and (EndA >= StartB)

SELECT `mainevents`.`maineventid`   AS `MainEventID`, 
       `mainevents`.`maineventcode` AS `MainEventCode`, 
       `mainevents`.`datestart`     AS `DateStart`, 
       `mainevents`.`dateend`       AS `DateEnd`, 
       `mainevents`.`location`      AS `Location`, 
       `mainevents`.`name`          AS `Name` 
FROM   `mainevents`, 
       `mainevents_lookup` 
WHERE  `mainevents`.`maineventid` = `mainevents_lookup`.`maineventid` 
  AND `mainevents`.`categoryid` = 1 
  AND `mainevents_lookup`.`datestart` <= '2016-05-27 11:59:59'     
  AND `mainevents_lookup`.`dateend` >= '2016-02-27 00:00:00'
LIMIT  0, 30

Upvotes: 1

Related Questions