Como obter registros que atendam às condições armazenadas em outras tabelas

Uma solução seria agrupar o SELECT que você já possui em outro SELECT e então filtrar as diferenças na seleção externa usando a cláusula WHERE.

Algo assim:

SELECT *
FROM 
  (
    SELECT
      requirements.req_id,
      (
        SELECT 
          COUNT (DISTINCT test_cases.test_id) 
        FROM test_cases, links 
        WHERE links.req_id = requirements.req_id 
            AND links.test_id = test_cases.test_id 
            AND test_cases.status != 'cancelled'
      ) as cnt_test_cases,
      (
        SELECT 
          /*
          This seems wrong. You likely don't want to 
          squash different 'passed' results into single number?
          */
          COUNT (DISTINCT results.result) 
        FROM results, links 
        WHERE links.req_id = requirements.req_id 
            AND links.test_id = results.test_id 
            AND results.result = 'passed'
      ) as cnt_results
     FROM dbo.requirements
  ) x
WHERE cnt_test_cases = cnt_results
  /* you might want to put in here something like 
    AND cnt_results > 0
  to make sure there are are at least some passed requirements
  */

Violino

Ao executar a seguinte consulta, podemos encontrar a matriz do seu conjunto de registros

select r.[req_id], r.[status] req_status,
l.[link_id], l.[req_id] link_req_id, l.[test_id] link_test_id,
t.[test_id], t.[status] test_status,
s.[result_id], s.[test_id] result_test_id, s.[result] from [dbo].[requirements] r
left join [dbo].[links] l on r.req_id=l.req_id
left join [dbo].[test_cases] t on l.test_id=t.test_id
left join [dbo].[results] s on t.test_id=s.test_id

como mostrado na imagem

Acho que você está tentando descobrir quantos testes cancelados e aprovados dependem do requisito. A consulta teve alguns erros, eu os comento.

SELECT DISTINCT requirements.req_id,
    
(SELECT COUNT (DISTINCT test_cases.test_id) 
FROM test_cases, links 
WHERE links.req_id = requirements.req_id AND 
links.test_id = test_cases.test_id AND 
test_cases.status != 'cancelled') as cnt_cancelled_test_cases,
--cnt_test_cases,
    
(SELECT COUNT (DISTINCT results.result) 
FROM results, links 
WHERE links.req_id = requirements.req_id AND 
links.test_id = results.test_id AND 
results.result = 'passed') as cnt_passed_test_cases
 --) as cnt_results
    
FROM requirements
--Having cnt_test_cases = cnt_results

O resultado é o seguinte