How to get records which meet conditions stored in another tables

Problem: How to get passed requirements where there are 4 tables in such relation\ requirements->linksresults<-result Scenario: Table1 - requirements |req_id |status| |:-------:|:----:| |req-1 |open | |req-2 |open | Table2 - test_cases |test_id |status | |:------------:|:-------:| |tc-1 |open | |tc-2 |cancelled| Table3 - links |link_id |req_id |test_id | |:-------:|:------:|:------:| |link-1 |req-1 |tc-1 | |link-2 |req-1 |tc-2 | Table4 - test results |result_id|test_id |result | |:-------:|:--------:|:---------:| |res-1 |tc-1 |passed | |res-2 |tc-2 |failed | So far I've tried such an approach to generate a table req_id | cnt_test_cases | cnt_results and treat requirement as passed when cnt_test_cases = cnt_results(='passed') but it doesn't work as these calculated columns doesn't exist for 'Having' statement at the end

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_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.reuslt = 'passed') as cnt_test_cases) as cnt_results

Having cnt_test_cases = cnt_results Maybe there is a better approach?