Can't figure out right SQL statement

Question

Short

Need to generate courses list and count

1. all
2. unanswered
3. answered but unchecked

Questions.

Detailed

For getting this result I need to operate with 7 tables.

UPDATE

Database structure

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

For full-sized image click here

I will explain some of them:

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.

But I can't figure out how final SQL statement will look like for all 3 problem.

Any suggestions? Ask if something unclear for you.

Answer 1

Unanswered questions: answered question is any question that has no answer in the answers table:

SELECT * 
FROM questions
WHERE id NOT IN (SELECT qid FROM answers)

Answered but unchecked questions:

SELECT *
FROM questions q
INNER JOIN 
(
    SELECT * 
    FROM answers
    WHERE id NOT IN answer_chk_results    -- unchecked answer
) a ON q.id = a.qid                       -- only answered questions

Edit: to get a list of courses with the unanswered, unchecked, all questions counts in one query:

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