***************************************************
*orders
***************************************************
*orderID ¦ orderStatusID ¦other non relavant fields
*----------------------------------------
* 1 ¦ 1 ¦ stuff
*
*****************************************************

*****************************************************
*coasters
*****************************************************
*coasterID ¦ orderID ¦ other non relavant fields
*****************************************************
* 1 ¦ 1 ¦ stuff
* 2 ¦ 1 ¦ stuff
* 3 ¦ 1 ¦ stuff
*************************************************

*****************************************************
*orderFiles
*****************************************************
*orderFileID ¦ orderID ¦ other non relavant fields
*****************************************************
* 1 ¦ 1 ¦ stuff
* 2 ¦ 1 ¦ stuff
* 3 ¦ 1 ¦ stuff

*****************************************************
*coasterProofFiles
*****************************************************
*proffFileID ¦ coasterID ¦ other non relavant fields
*****************************************************
* 1 ¦ 1 ¦ stuff
* 2 ¦ 2 ¦ stuff
* 3 ¦ 3 ¦ stuff

*****************************************************
*orderStatuses
*****************************************************
*orderStatusID¦ orderStatusName¦ other non relavant fields
*****************************************************
* 1 ¦ New ¦ stuff
* 2 ¦ Pending ¦ stuff
* 3 ¦ Shipped ¦ stuff

Im trying to select the count of order files, the count of coasterProofFiles, the count of coasters, and the order status name grouped by orderID.
Now, this query works and returns the desired counts[ (count(c.coasterID) = 3, fileCount=3, proofFileCount=3]

SELECT o.*,
o.orderID as mainOrderID,
count(c.coasterID),
os.orderStatusName,
(SELECT count(of.orderFiles) from orderFiles as of WHERE orderID=mainOrderID)as fileCount,
(SELECT count(cpf.proofFileID) from coasterProofFiles as cpf WHERE cpf.coasterID IN
(SELECT coasterID FROM coasters WHERE orderID =mainOrderID))as proofFileCount
FROM
orders as o
LEFT JOIN coasters as c on c.orderID=o.orderID
LEFT JOIN orderStatuses as os on o.orderStatusID=os.orderStatusID
WHERE
o.orderStatusID <=4 group by o.orderID

But its ugly, and i would REALLY like to avoid all the sub-queries (as the site will have 1000's of rows).

my attempt to clean it up is the following:
SELECT o.*,
os.orderStatusName ,
count(of.orderFiles),
count(c.coasterID),
count(cpf.proofFileID)
FROM
orders as o JOIN
orderStatuses as os
ON o.orderStatusID = os.orderStatusID
LEFT JOIN orderFiles as of on of.orderID = o.orderID
LEFT JOIN coasters as c on c.orderID = o.orderID
LEFT JOIN coasterProofFiles as cpf ON c.coasterID = cpf.coasterID
WHERE o.orderStatusID <= 4
GROUP BY o.orderID
ORDER BY o.orderStatusID

but it returns all of the counts as 9 .. its multplying the results together and i cant figure out why.

can any sql guru's out there explain to me why this is happening, and how i could acheive the results of my first query, without all the sub-queries?