How to replace sub-query by joins?
<p>How can i get rif of theses subqueries ?</p>

<p>(all tables have columns Created and LastEdited as timestamps)</p>

<pre><code>table Process
 - ID
 - Title

table ProcessHistory
 - ID
 - ProcessID
 - HistoryID

table History
 - ID
 - Title (new, open, closed etc.) 
</code></pre>

<p>When i try to get a list of processes with cols of the last status title i do:</p>

<pre><code>SELECT DISTINCT Process.*, History.Title AS HistoryTitle, History.ID AS HistoryID
FROM `Process`
LEFT JOIN ProcessHistory AS ProcessHistory ON Process.ID=ProcessHistory.ProcessID
LEFT JOIN History AS History ON HistoryID=ProcessHistory.HistoryID
WHERE History.ID = (
    SELECT HistoryID FROM ProcessHistory 
    WHERE ProcessID=Process.ID 
    ORDER BY ProcessHistory.ID DESC LIMIT 1
)
GROUP BY Process.ID
ORDER BY Process.ID DESC LIMIT 0, 100
</code></pre>

<p>When i try to get a list filtered by a specific status
(Where the latest HistoryID is 1 - "all open Processes")</p>

<pre><code>SELECT DISTINCT Process.*, History.Title AS HistoryTitle, History.ID AS HistoryID
FROM `Process`
LEFT JOIN ProcessHistory AS ProcessHistory ON Process.ID=ProcessHistory.ProcessID
LEFT JOIN History AS History ON HistoryID=ProcessHistory.HistoryID
WHERE History.ID=(
    SELECT HistoryID FROM ProcessHistory 
    WHERE HistoryID =1   
    AND ProcessID=Process.ID ORDER BY ProcessHistory.ID DESC LIMIT 1
)
GROUP BY Process.ID
ORDER BY Process.ID DESC LIMIT 0, 100
</code></pre>

<p>For performance reasons i want to get rid of theses subqueries?
How can i replace the subquery? </p>

<p>Thanks in advance !</p>