I have two tables, say t1 and t2. During the select on t1 I want to count the number of rows in t2 that have a matching ID to the relevant column in t1. I'm sure this is possible with a join, but my SQL skills are weak.

Here is what I have so far

SELECT t1.id, t1.title,
COUNT(t2.id_comment) AS num_comments
FROM articles AS t1
LEFT JOIN comments AS t2
ON t2.id_article = t1.id
WHERE id = '".$_GET['id']."'
GROUP BY t2.id_comment

This doesn't give the results I expect, in fact it gives some confusing results. There might be some syntax issues as I've removed some irrelevant parts of the query.

    SELECT t1.id, t1.title,
COUNT(t2.id_comment) AS num_comments
FROM articles AS t1
LEFT JOIN comments AS t2
ON t2.id_article = t1.id
WHERE id = '".$_GET['id']."'
GROUP BY t1.id, t1.title

    When doing a group by all non-aggregated columns in the select clause must be present in the group by clause.

      Thanks, I'll give it a whirl when I get home tonight.

        Write a Reply...