How do you join tables at different points based on data in the first table?
Dont know why you have a subselect at all, that query can be reduced to this: 

SELECT * FROM shift AS a 
LEFT JOIN shift AS b ON a.replaced_id = b.id 
LEFT JOIN schedule AS s ON a.schedule_id = s.id AND s.date &gt;= '2015-12-05' 
left JOIN shift_info AS si ON s.shift = si.initial AND si.date &lt;= '2015-12-05'
WHERE a.user_id = 58;


Generally you want join conditions applied in the ON clause, particularly when they affect the actual join, as is the case with LEFT JOIN. I think this fulfills what you are asking as well (since the schedule and shift_info start at the same start time).