Converting subquery to join

I have three table viz, chapters, lessons, and questions that are used to show questions for a course. The table structure is given below for reference.

Table "public.chapters"

  Column   |          Type          |                           Modifiers                           
-----------+------------------------+---------------------------------------------------------------
 id   开发者_开发知识库     | integer                | not null default nextval('chapters_chapter_id_seq'::regclass)
 chapter   | character varying(255) | not null
 course_id | integer                | 
 published | boolean                | default false

Table "public.lessons"

   Column   |          Type          |                          Modifiers                          
------------+------------------------+-------------------------------------------------------------
 id         | integer                | not null default nextval('lessons_lesson_id_seq'::regclass)
 lesson     | character varying(255) | not null
 chapter_id | integer                | 
 published  | boolean                | default false

Table "public.questions"

  Column   |          Type          |                            Modifiers                            
-----------+------------------------+-----------------------------------------------------------------
 id        | integer                | not null default nextval('questions_question_id_seq'::regclass)
 template  | character varying(255) | not null
 wording   | character varying(255) | not null
 lesson_id | integer                | not null

Now, I need to find number of questions in a chapter. So, I use following query.

select sum( num_of_questions ) as num_of_questions, 
       chapter_id 
 from ( select chapters.id as chapter_id,
               lesson_id, 
               count(*) as num_of_questions 
          from questions 
          JOIN lessons ON lessons.id = questions.lesson_id 
          JOIN chapters ON lessons.chapter_id = chapters.id 
      GROUP BY lesson_id, chapters.id  
      ORDER BY lesson_id, chapters.id) as foo 
group by  chapter_id;

How, could I convert this query to use join instead of subquery.

Use:

  SELECT c.id as chapter_id,
         COUNT(*) as num_of_questions 
    FROM CHAPTERS AS c
    JOIN LESSONS AS l ON l.chapter_id = c.id
    JOIN QUESTIONS AS q ON q.lesson_id = l.id
GROUP BY c.id

There's no need for a subquery -- just relax the GROUP BY clause.

The ORDER BY in your original query does nothing for you, wastes resources -- unless you use LIMIT.