Hello I have question to the mysql query (DQL)
Table:
id| start | end | day
-------------------------------
1 | 10:00 | 11:00 | 03-05-2013
2 | 11:00 | 12:00 | 03-05-2013
3 | 10:30 | 12:00 | 03-05-2013
4 | 13:00 | 14:00 | 03-05-2013
5 | 14:00 | 15:00 | 03-05-2013
And as a result i want to get (columns: id, start, end) only rows which have the same part between start and end. So as a result i would like to get:
id| start | end
------------------
1 | 10:00 | 11:00
2 | 11:00 | 12:00
3 | 10:30 | 12:00
Because rows 1 and 3 have same time between: 10:30 and 11 and rows 2,3 have the same time between 11:00 to 12:00
I Hope it's clear what i want to get as an output. Would be, great to get answer in DQL syntax for doctrine.
You need to self-join the table and look for conflicts between pairs
SELECT
t1.id,
t1.start,
t1.end
FROM table_name t1
JOIN table_name t2
WHERE
t1.start < t2.end AND
t2.start < t1.end AND
t1.id <> t2.id AND
t1.date = t2.date
You can use a SELECT DISTINCT or a GROUP BY to trim the duplicates in the output.