CODEWITHSUNDEEP
mysqldate
gurluk
gurluk

Reputation: 195

Find Overlapping Dates Give Infinite Loop MySQL

Let's assume I have one table with given dates:

+-----------+-----------+----+
|StartDate  |EndDate    |ID  |
+-----------+-----------+----+
|2013-08-29 |2014-12-29 |1   |
|2013-08-29 |2013-09-31 |2   |
|2015-01-02 |2015-03-20 |3   |
+-----------+-----------+----+

I want to find the conflicting dates, so the code should give me 1 as the conflicting dates. When I write this code in MySQL, it enters an infinite loop, which I would appreciate a little help, because I cannot see why it does not work:

select t1.* from dates t1
inner join dates t2
on t2.StartDate > t1.StartDate
and t2.StartDate < t1.EndDate;

Thank you.

Upvotes: 1

Views: 88

Answers (2)

Alex
Alex

Reputation: 17289

Shouldn't it be?:

select t1.*, t2.id as conflict_id
from dates t1
inner join dates t2
on t2.StartDate >= t1.StartDate
and t2.EndDate <= t1.EndDate
and t2.id != t1.id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

It doesn't enter an infinite loop. It just takes a long, long time. However, this might be a better route:

select d.*
from dates d
where exists (select 1
              from dates d2
              where d2.StartDate < d1.EndDate and
                    d2.EndDate > d1.StartDate
             );

And index on dates(StartDate, EndDate) might help the query.

Upvotes: 0

Related Questions