SQL Server Management Studio – Retrieve one parent row regardless of number of children when filtering on children

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%'