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?

Can you add some sample data + desired output? (in TEXT, no pictures) — Luuk, Aug 22 '20 at 17:50
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 22 '20 at 17:58

score 1 · Accepted Answer · answered Aug 22 '20 at 17:53

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

answered Aug 22 '20 at 17:53

Luuk

12,245
5
22
33

Are you sure it should be `count('replyid')`? – Dharman Aug 22 '20 at 17:59
`count` does do a count and the question was "which counts the number of replies", so what could be wrong with it? – Luuk Aug 22 '20 at 18:13
You are counting a literal string – Dharman Aug 22 '20 at 18:16
`select count(*) from mytable;` and `select count('42') from mytable` both produce the same count. – Luuk Aug 22 '20 at 18:17
How about using a different value e.g. `COUNT(1)` or as you said `COUNT(*)` to make it less confusing? – Dharman Aug 22 '20 at 18:19
Basically what i did was copying/pasting from the question. Most of the times, when i start counting, i am doing a `SELECT count(*) FROM....` – Luuk Aug 22 '20 at 18:21

Two Left Joins and including a Count?

1 Answers1