Let’s say you want to report on each parent row, but must join a child row to do filtering on the child row, but you only want to return one row per parent. The following SQL will not work, but instead repeats the parent row per number of children filtered on.
select a.test_name, b.question_type from tests a join test_questions b on b.test_guid = a.test_guid where a.test_name like 'Exodus 1 - 5%' and b.question_type = 'Multiple Choice'
The above result reflects that there are 12 multiple choice questions (child of tests table) for every parent test (tests table) where the test name is like ‘Exodus 1 – 5%’. What I want to accomplish is to simply have the results reflect how many tests are there that have a multiple choice question.
To accomplish this, we must instead join the questions table as a sub select using ROW_NUMBER OVER PARTITION BY method where the rownumber = 1 to ensure that we only retrieve 1 child one, hence one parent row.
select a.test_name, b.question_type from tests a join (select test_questions.test_guid, test_questions.question_type, ROW_NUMBER() OVER (PARTITION BY test_questions.test_guid order by test_questions.test_guid) As RowNumber from test_questions where test_questions.question_type = 'Multiple Choice') b on a.test_guid = b.test_guid where rownumber = 1 and a.test_name like 'Exodus 1 - 5%'