I'm not sure if this is possible but I'm trying to right a compound query as seen below. I'm running in trouble in trying to include the second select in the from clause.

Code:
SELECT * FROM Tickets t JOIN Computers ON t.cid=Computers.cid JOIN Sites ON Computers.sid = Sites.sid, (SELECT COUNT(a.tid) FROM Actions a WHERE a.tid = t.tid GROUP BY a.tid) As ActionCount WHERE t.pid = 15 ORDER BY t.submitDate DESC;
In this example I get an error saying "Unknown column 't.tid' in 'where clause'".

I'm not quite sure how to handle the various alias when dealing with multiple joins and a correlated sub query.

Thanks for any suggestions you might offer.