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
