Having trouble with MySQL query

Question

Need to generate courses list and count

1. all
2. unanswered
3. answered but unchecked

Questions.

Here's my query

SELECT c.id, 
    c.name, 
    COUNT(allq.id) 'All', 
    COUNT(unanswered.id) 'Unanswered',
    COUNT(unchecked.id) 'Unchecked'
FROM courses c
LEFT JOIN `courses-lessons` cl 
    ON c.id = cl.cid
LEFT JOIN lesson_questions allq 
    ON cl.id = allq.lid
LEFT JOIN
(
    SELECT q.id, a.qid, q.lid
    FROM lesson_questions q
    LEFT JOIN 
    (
        SELECT id, qid
        FROM answers
        WHERE id NOT IN (SELECT aid FROM answer_chk_results)
    ) a 
        ON q.id = a.qid 
) unchecked 
    ON cl.id = unchecked.lid
LEFT JOIN
(
    SELECT id
    FROM lesson_questions
    WHERE id NOT IN (SELECT qid FROM answers)
) unanswered 
    ON cl.id = unchecked.lid
GROUP BY c.id, c.name

and here's an SQL Fiddle

Database structure

https://docs.google.com/open?id=0B9ExyO6ktYcOenZ1WlBwdlY2R3c

I added 3 questions as sample data, but query calculates all questions and returns 9. It's impossible. Something goes wrong. Can't figure out, what am I missing.

Update

I will explain some of tables:

1. answer_chk_results - checked answers table. So if some answer doesn't exist on this table, it means it's unchecked
2. lesson_questions - lesson <-> question associations (by id) table
3. courses-lessons - courses <-> lessons associations (by id) table

Only first problem seems not so difficult: To get all questions' count of course, my plan looks like below:

1. At first, we need to get all courses names list. Query will look like so:

   SELECT c.id, c.name FROM courses c

2. Then get all lessons from courses-lessons association table by every selected course from 1. (Have no idea how to continue previous query)

3. Then, count all questions by selected lesson id (lid column) from 2.

Answer 1

You can do this by using conditional count statements, so you don't need all the sub queries:

SELECT  c.ID,
        c.Name,
        COUNT(DISTINCT q.ID) AS Questions,
        COUNT(DISTINCT CASE WHEN a.ID IS NULL THEN q.ID END) AS UnAnswered,
        COUNT(DISTINCT CASE WHEN cr.ID IS NULL AND a.ID IS NOT NULL THEN q.ID END) AS UnChecked
FROM    Courses c
        LEFT JOIN `Courses-Lessons` cl
            ON cl.CID = c.ID
        LEFT JOIN Lesson_Questions q
            ON cl.ID = q.LID
        LEFT JOIN Answers a
            ON a.QID = q.ID
        LEFT JOIN Answer_chk_results cr
            ON a.ID = cr.AID
GROUP BY c.ID --, c.Name

SQL Fiddle

---

EDIT

SELECT  c.ID,
        c.Name,
        COUNT(DISTINCT q.ID) AS Questions,
        COUNT(DISTINCT CASE WHEN a.ID IS NULL THEN q.ID END) AS UnAnswered,
        COUNT(a.ID) AS Answers,
        COUNT(DISTINCT CASE WHEN cr.ID IS NULL THEN a.ID END) AS UnChecked
FROM    Courses c
        LEFT JOIN `Courses-Lessons` cl
            ON cl.CID = c.ID
        LEFT JOIN Lesson_Questions q
            ON cl.LID = q.LID
        LEFT JOIN Answers a
            ON a.QID = q.QID
        LEFT JOIN Answer_chk_results cr
            ON a.ID = cr.AID
GROUP BY c.ID, c.Name

Answer 2

Maybe - http://sqlfiddle.com/#!2/d23b4/42

SELECT  c.ID,
        c.Name,
        COUNT(q.ID) AS 'Questions',
        SUM(CASE WHEN a.ID IS NOT NULL Then 1 ELSE 0 END ) as 'Unanswered',
        SUM(CASE WHEN cr.ID IS NOT NULL Then 1 ELSE 0 END ) as 'Unchecked'
FROM    Courses c
        LEFT JOIN `Courses-Lessons` cl
            ON cl.CID = c.ID
        LEFT JOIN Lesson_Questions q
            ON cl.ID = q.LID
        LEFT JOIN Answers a         
            ON a.QID = q.ID             
        LEFT JOIN Answer_chk_results cr
            ON a.ID = cr.AID
GROUP BY c.ID