SQL Server Management Studio – Retrieve one child row regardless of number of grand children from another child table with or without filtering

Let’s say you want to report on each child row, but you only want to see one row based on a grand child row with filtering on the grand child row. The following SQL will not work, but instead gets all the child rows of the parent row.

select b.test_name, a.question_type, a.test_question_guid, c.answer
   from test_questions a
   join tests b on b.test_guid = a.test_guid
   join test_multiple_choice_answers c on c.test_question_guid = a.test_question_guid
  where b.test_name like 'Exodus 1 - 5%'
    and a.question_type = 'Multiple Choice'
    and c.answer = 'Aaron'
  order by c.answer

The above result reflects multiple children based on the test_question_guid, but brings me back 3 rows where the answer is Aaron when I only want to see 1 row.

To accomplish this, I join the grand child table called multiple choice and join the child and grand child using the test_question_guid as a sub select using ROW_NUMBER OVER PARTITION BY method where the rownumber = 1 to ensure that we only retrieve 1 grand child one, hence one child row.

select f.* from(
 select b.test_name, a.question_type, a.test_question_guid, c.answer
   from test_questions a
   join tests b on b.test_guid = a.test_guid
   join (select *, row_number() over (partition by test_question_guid order by test_question_guid) as rownumber from test_multiple_choice_answers) c on a.test_question_guid = c.test_question_guid
   where c.answer = 'Aaron'
    and isnull(rownumber,1)=1) f
    where f.test_name like 'Exodus 1 - 5%'
      and f.question_type = 'Multiple Choice'
    order by f.answer
Please follow and like us: