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