title: Nested Select or Inner Join including rows from another table
body: <p>I have a pretty complex select statement that returns counted statistics from tables (think of it as an answer bank -- the complex select statement below) using inner join. </p>
  
  <p>These answers are related to a table called Questions_Bank_AnswerChoices (which stores all the questions).  </p>
  
  <p>I am attempting to first pull the Questions (from the table Questions_Bank_AnswerChoices) then match them up with the statistics (complex statement below).   The complex statement below pulls the statistics, but does not pull the questions unless they have been answered.  </p>
  
  <p>So, if no one answers question1, then question one will not show up in the statistics because it is not included in the Answers table (bc no one answered it).  </p>
  
  <p>How can I achieve this?  I think that I need to outer join?</p>
  
  <p>Complex Select Statement:</p>
  
  <pre><code>WITH tbl as (
     SELECT 
        Questions_Bank.QuestionID, Questions_Bank.QuestionName,   
        REPLACE(Schools_Answers_Items.AnswerValue, '? ', ', ') as AnswerValue, 
        COUNT(Schools_Answers_Items.SchoolsAnswersItemID) AS CountAnswer,
        Schools_Answers_Items.SchoolID 
     FROM Questions_Bank 
     INNER JOIN Schools_Answers_Items
       ON Questions_Bank.QuestionID = Schools_Answers_Items.QuestionID 
     LEFT OUTER JOIN Schools_Answers  
       ON Schools_Answers_Items.SchoolsAnswerID = Schools_Answers.SchoolsAnswerID 
     WHERE (Questions_Bank.QuestionID = 1108) 
       AND (Schools_Answers.SchoolID = 103) 
     GROUP BY 
        Schools_Answers_Items.SchoolID,         
        Schools_Answers_Items.AnswerValue,
        Questions_Bank.QuestionID, 
        Questions_Bank.QuestionName
  ) 
  SELECT 
     QuestionID, QuestionName, AnswerValue, CountAnswer, 
     SUM(CountAnswer) OVER () AS CountAllAnswers  
  FROM tbl
  </code></pre>