SQL COUNT with values from 2 tables

Question

I have following two tables:

comments

comment_post_ID	status	comment_type
1	complete	course_status
2	complete	course_status
a1	complete	lesson_status
a2	passed	lesson_status
a3	in-progress	lesson_status

postmeta

post_id	meta_key	meta_value
a1	lesson_course	1
a2	lesson_course	1
a3	lesson_course	2

The Result should look like:

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

    [1] => stdClass Object
        (
            [comment_post_ID] => 2
            [lessoncomplete] => 0
        )

What I want is the count of all lessons with status complete or passed by the comment_post_ID. The postmeta table is needed for which lesson belongs to which course. That's where I am so far:

SELECT
c.comment_post_ID,

/* Lektionen abgeschlossen*/
(SELECT Count(*) FROM comments WHERE comment_type = 'lesson_status' AND status IN ('passed', 'complete')) AS lessoncomplete

FROM comments c

WHERE c.comment_type = 'course_status'

I don't even know if I'm on the right way so every help is much appreciated! Later on I also want to calculate something in the same query with the count of the completed lessons...

Answer 1

I have crafted some SQL Schema from your example: I think this query might bring you closer to what you desire:

Schema (MySQL v5.7)

CREATE TABLE IF NOT EXISTS `postmeta` (
`post_id` varchar(200) NOT NULL,
`meta_key` varchar(200) NOT NULL,
`meta_value` varchar(200) NOT NULL,
PRIMARY KEY (`post_id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES
 ('a1', 'lesson_course', '1'),
  ('a2', 'lesson_course', '1'),
  ('a3', 'lesson_course', '2');
  
  
CREATE TABLE IF NOT EXISTS `comments` (
`comment_post_ID` varchar(200) NOT NULL,
`status` varchar(200) NOT NULL,
`comment_type` varchar(200) NOT NULL,
PRIMARY KEY (`comment_post_ID`)
) DEFAULT CHARSET=utf8;
INSERT INTO `comments` (`comment_post_ID`, `status`, `comment_type`) VALUES
  ('1', 'complete', 'course_status'),
  ('2', 'complete', 'course_status'),
  ('a1', 'complete', 'lesson_status'),
  ('a2', 'passed', 'lesson_status'),
  ('a3', 'in-progress', 'lesson_status');
 

---

Query #1

SELECT
(
SELECT COUNT(comment_post_ID) FROM comments c
WHERE c.status = 'passed'
)+(
SELECT COUNT(comment_post_ID) FROM comments c
WHERE c.status = 'complete') AS Lesson_Completed;


| Lesson_Completed |
| ---------------- |
| 4                |

---

View on DB Fiddle

Why do you need table postmeta?