SQl Count rows with 2 conditions on join-CodePudding

I have following two tables:

comments

comment_post_ID	comment_type
1	course_status
2	course_status

postmeta

meta_key	meta_value
lesson_course	1
lesson_course	1
lesson_course	2

The Result should look like:

Array
(
    [0] => stdClass Object
        (
            [comment_post_ID] => 1
            [lessoncount] => 1
        )

    [1] => stdClass Object
        (
            [comment_post_ID] => 1
            [lessoncount] => 2
        )

And that's where I'm so far

SELECT
c.comment_post_ID,
C.lessoncount

FROM comments c

INNER JOIN (SELECT meta_value, count(meta_value) as lessoncount
FROM  postmeta WHERE meta_key='lesson_course') C 

WHERE c.comment_type = 'course_status'

The problem is I'm still getting the same lessoncount on every result. I tried so many things but I don't get... Every help much appreciated!

CodePudding user response：

You're not grouping the counts, so you're getting the total count of all the lesson_course rows in postmeta.

And you have no joining condition between the two tables, so you get a full cross product.

You don't need the subquery, you can do it with an ordinary join and grouping in the main query.

SELECT c.comment_post_id, COUNT(*) AS lessoncount
FROM comments AS c
JOIN postmeta AS m ON c.comment_post_id = m.meta_value
WHERE c.comment_type = 'course_status'
AND m.meta_key = 'lesson_course'
GROUP BY c.comment_post_id