Two Left Joins and including a Count?

Question

I've got the following query which includes a left join which concatenates multiple tags. This query works.

  SELECT
    ap.userid, 
    ap.audioid,      
    u.id
       (
        SELECT GROUP_CONCAT(t.tagname)
        FROM entitytag et
        LEFT JOIN tags t on t.tagid = et.tagid
        WHERE et.audioid = ap.audioid
    ) tagname
FROM audioposts ap 
LEFT JOIN users u ON u.id = ap.userid

But now I'd like to add another left join which counts the number of replies to each ap.audioid from a table called replies. Anybody know how to add this extra left join?

Here's the query which works to count replies to a specified audioid from GET;

SELECT count('replyid') from replies WHERE opid = ?

But how do I add the extra left join to the first code so that I can fetch the reply count from the replies table for each audioid?

Answer 1

no left join but reading your question this should work:

SELECT
    ap.userid, 
    ap.audioid,      
    u.id,
       (
        SELECT GROUP_CONCAT(t.tagname)
        FROM entitytag et
        LEFT JOIN tags t on t.tagid = et.tagid
        WHERE et.audioid = ap.audioid
    ) tagname,
    (
      select count('replyid') from replies WHERE opid = ap.audioid
    ) as count
FROM audioposts ap 
LEFT JOIN users u ON u.id = ap.userid