How to reduce the need for these MySQL subqueries?
SELECT
p.something,
p.otherthing,
c.athing,
d.nothing,
(select startdate from dbo.aqcprojects where projectid = p.id and aqcphase = 1) as 'Phase 1 start',
(select finishdate from dbo.aqcprojects where projectid = p.id and aqcphase = 1) as 'Phase 1 end',
(select startdate from dbo.aqcprojects where projectid = p.id and aqcphase = 2) as 'Phase 2 start',
(select finishdate from dbo.aqcprojects where projectid = p.id and aqcphase = 2) as 'Phase 2 end',
 **the above four lines repeated ad nauseum.
FROM
bunch of joins

SELECT
p.something,
p.otherthing,
c.athing,
d.nothing,
project_phase_1.startdate as 'Phase 1 start',
project_phase_1.finishdate as 'Phase 1 end',
project_phase_2.startdate as 'Phase 2 start',
project_phase_2.finishdate as 'Phase 2 end',
 **the above four lines repeated ad nauseum.
FROM
projects AS p
LEFT JOIN dbo.aqcprojects AS project_phase_1 ON project_phase_1.projectid = p.id
LEFT JOIN dbo.aqcprojects AS project_phase_2 ON project_phase_2.projectid = p.id
bunch of joins
WHERE project_phase_1.aqcphase = 1 AND project_phase_2.aqcphase = 2