title: SQL Left Join Multiple Tables and count values conditionally in each table
body: <p>I am having some trouble putting together a SQL statement properly because I don't have much experience SQL, especially aggregate functions. Safe to say I don't <em>really</em> know what I'm doing outside of the basic SQL structure. I can do regular joins, but not complex ones.</p>
  
  <p>I have some tables: <strong>'Survey'</strong>, <strong>'Questions'</strong>, <strong>'Session'</strong>, <strong>'ParentSurvey'</strong>, and <strong>'ParentSurveyQuestion'</strong>. Structurally, a survey can have questions, it can have users that started the survey (a session), and it can have a parent survey whose questions get imported into the current survey.</p>
  
  <p>What I want to do is get information for a each survey in the Survey table; total questions it has, how many sessions have been started (conditionally, ones that have not finished), and the number of questions in the parents survey. The three joined tables can but do not have to contain any values, and if they don't then 0 should be returned by COUNT. The common field in three of the tables is a variation of <strong>'survey_id'</strong></p>
  
  <p>Here is my SQL so far, I put the table structure below it.</p>
  
  <pre><code> SELECT 
     `kp_survey_id`,
     COALESCE( q.cnt, 0 ) AS questionsAmount,
     COALESCE( s.cnt, 0 ) AS sessionsAmount
     COALESCE( p.cnt, 0 ) AS parentQAmount,
   FROM `Survey`
     LEFT JOIN                    &lt;-- I'd like the count of questions for this survey
        ( SELECT COUNT(*) AS cnt 
        FROM Questions
        GROUP BY kf_survey_id ) q
       ON Survey.kp_survey_id = Questions.kf_survey_id
     LEFT JOIN 
        ( SELECT COUNT(*) AS cnt    &lt;-- I'd like the count of started sessions for this survey
        FROM Session
        WHERE session_status = 'started'  &lt;-- should this be Session.session_status?
        GROUP BY kf_survey_id ) s
       ON Survey.kp_survey_id = Session.kf_survey_id
     LEFT JOIN            
        ( SELECT COUNT(*) AS cnt    &lt;-- I'd like the count of questions in the parent survey with this survey id
        FROM ParentSurvey
        GROUP BY kp_parent_survey_id ) p
       ON Survey.kf_parent_survey_id = ParentSurveyQuestion.kf_parent_survey_id
  </code></pre>
  
  <p>'kp' prefix means primary key, while 'kf' prefix means foreign key<br>
  Structure:  </p>
  
  <p><strong>Survey:</strong> 'kp_survey_id' | 'kf_parent_survey_id'  </p>
  
  <p><strong>Question:</strong> 'kp_question_id' | 'kf_survey_id'  </p>
  
  <p><strong>Session:</strong>  'kp_session_id' | 'kf_survey_id' | 'session_status' </p>
  
  <p><strong>ParentSurvey:</strong>  'kp_parent_survey_id'  | 'survey_name'</p>
  
  <p><strong>ParentSurveyQuestion:</strong>  'kp_parent_question_id' | 'kf_parent_survey_id' </p>
  
  <p>There are also other columns in each table like 'name' or 'account_id', but i don't think they matter in this case</p>
  
  <p>I'd like to know if I'm doing this correctly or if I'm missing something. I'm repurposing some  code I found here on stackoverflow and modifying it to meet my needs, as I haven't seen conditional aggregation for more than three tables on this site. </p>
  
  <p>My expected output is something like:  </p>
  
  <pre><code>kp_survey_id   |   questionsAmount   |   sessionsAmount   |   parentQAmount  
      1          |         3           |         0          |        3
      2          |         0           |         5          |        3
  </code></pre>