How do you join tables at different points based on data in the first table? Complicated question:
I have 3 tables that I am joining to pull data. 
The first is a 'shift' table; the second a 'schedule' table, and the last is a 'shift_info' table.

shift_info

+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| date                 | date         | NO   | MUL | NULL    |       |
| name                 | varchar(20)  | YES  |     | NULL    |       |
| initial              | varchar(1)   | YES  |     | NULL    |       |
| start_time           | time         | YES  |     | NULL    |       |
| end_time             | time         | YES  |     | NULL    |       |
+----------------------+--------------+------+-----+---------+-------+


shift

+--------------------+----------+------+-----+---------+----------------+
| Field              | Type     | Null | Key | Default | Extra          |
+--------------------+----------+------+-----+---------+----------------+
| id                 | int(8)   | NO   | PRI | NULL    | auto_increment |
| schedule_id        | int(6)   | NO   | MUL | NULL    |                |
| user_id            | int(3)   | NO   |     | NULL    |                |
| created_on         | datetime | NO   |     | NULL    |                |
| replaced_id        | int(8)   | YES  |     | NULL    |                |
| created_by_user_id | int(3)   | NO   |     | NULL    |                |
+--------------------+----------+------+-----+---------+----------------+


schedule

+--------------+---------+------+-----+------------+----------------+
| Field        | Type    | Null | Key | Default    | Extra          |
+--------------+---------+------+-----+------------+----------------+
| id           | int(7)  | NO   | PRI | NULL       | auto_increment |
| date         | date    | NO   | PRI | 0000-00-00 |                |
| shift        | char(1) | NO   | PRI |            |                |
| shift_id     | int(3)  | YES  |     | NULL       |                |
+--------------+---------+------+-----+------------+----------------+


The schedule table stores a list of shifts, identifed by an initial. 
The shift table stores the details of shift. 
The shift info table lists the shifts available at a certain date.

SELECT * FROM shift as a 
LEFT JOIN shift as b ON a.replaced_id = b.id 
LEFT JOIN schedule ON a.schedule_id = schedule.id 
left JOIN shift_info AS e ON schedule.shift = e.initial 
WHERE a.user_id = 58 
    AND schedule.date &gt;= 20151205 
    AND e.date = (SELECT DISTINCT date 
                  FROM shift_info 
                  WHERE date &lt;= 20151205 ORDER BY date DESC LIMIT 1);


My question is: is there a way to make the subquery search in 'shift_info' reflect the data in the joined table 'schedule'? Currently if the date in shift_info is greater than the date in schedule, the database looks up the wrong shift_info.

I would like to know if you can search one date from the shift_info table in a different way to how I am trying to limit it.
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).