title: Problem in getting this output using Join in SQL Server
body: <p>I want to show a output like this,</p>
  
  <pre><code>+------------+--------------+---------------------+---------------+-----------+--------------+-----------+
  | segment_id | segment_name | segment_description | tot_questions | tot_marks | marks_obtain | neg_marks |
  +------------+--------------+---------------------+---------------+-----------+--------------+-----------+
  | 10006      | MCQ SECTION  |                     | 5             | 20        |              | -.5       |
  | 10007      | Non-MCQ      |                     | 5             | 20        |              | 0         |
  +------------+--------------+---------------------+---------------+-----------+--------------+-----------+
  </code></pre>
  
  <p>Now here are my tables that will be used for join,</p>
  
  <p><strong>MockTestMaster</strong> (used column tot_marks)</p>
  
  <pre><code>+---------+------------+-------------+-------------+
  | test_id | test_name  | total_Marks | template_id |
  +---------+------------+-------------+-------------+
  | 1       | MOCKTEST-X | 20          | 1           |
  +---------+------------+-------------+-------------+
  </code></pre>
  
  <p><strong>MockTestDetails</strong> (segment_id, segment_name, tot_questions)</p>
  
  <pre><code>+---------+------------+--------------+------------------------+
  | test_id | section_id | section_name | total_section_question |
  +---------+------------+--------------+------------------------+
  | 1       | 10006      | MCQ SECTION  | 5                      |
  | 1       | 10006      | MCQ SECTION  | 5                      |
  | 1       | 10006      | MCQ SECTION  | 5                      |
  | 1       | 10006      | MCQ SECTION  | 5                      |
  | 1       | 10006      | MCQ SECTION  | 5                      |
  | 1       | 10007      | Non-MCQ      | 5                      |
  | 1       | 10007      | Non-MCQ      | 5                      |
  | 1       | 10007      | Non-MCQ      | 5                      |
  | 1       | 10007      | Non-MCQ      | 5                      |
  | 1       | 10007      | Non-MCQ      | 5                      |
  +---------+------------+--------------+------------------------+
  </code></pre>
  
  <p><strong>prd_template_question_type</strong> (used columns neg_marks)</p>
  
  <pre><code>+-------------+-------------+----------------+----------------+
  | template_id | is_mcq_type | is_nonmcq_type | marks_if_wrong |
  +-------------+-------------+----------------+----------------+
  | 1           | 1           | 0              | -0.5           |
  | 1           | 0           | 1              | 0              |
  +-------------+-------------+----------------+----------------+
  </code></pre>
  
  <p><strong>prd_templatesection</strong> (I could use this table,but on my application user could add a section and which will not be inserted in this table because it will change the original template,so I inserted that section in MockTestDetails table)</p>
  
  <pre><code>+-------------+------------+--------------+------------------------+-------------------+
  | template_id | section_id | section_name | total_section_question | sectiontype_isMcq |
  +-------------+------------+--------------+------------------------+-------------------+
  | 1           | 10006      | MCQ SECTION  | 5                      | true              |
  | 1           | 10007      | Non-MCQ      | 5                      | false             |
  +-------------+------------+--------------+------------------------+-------------------+
  </code></pre>
  
  <p>Now problem of getting my output is the <code>marks_if_wrong</code> column of <code>prd_template_question_type</code> table.</p>
  
  <p>Now here is the deal how to get neg_marks: if <code>sectiontype_isMcq</code> of <code>prd_templatesection</code> is <code>true</code>, then grab <code>marks_if_wrong</code> column of <code>prd_template_question_type</code>.</p>
  
  <p>I tried this query:</p>
  
  <pre><code>SELECT DISTINCT 
      md.section_id AS segment_id,
      md.section_name AS segment_name,
      '' AS segment_description,
      md.total_section_question as tot_questions,
      total_Marks AS tot_marks,
      '' AS marks_obtain,
      '' AS neg_marks
      --prd_template_question_type.marks_if_wrong AS neg_marks
  FROM
      dbo.MocktestDetails md
  INNER JOIN 
      dbo.MockTestMaster mm ON mm.test_id = md.test_id 
  INNER JOIN 
      prd_templatesection pts ON pts.template_id = mm.template_id
  WHERE 
      mm.test_id = 1;
  </code></pre>
  
  <p>Here is a <a href="http://sqlfiddle.com/#!18/8578b/2" rel="nofollow noreferrer">fiddle of this problem</a></p>