How to fetch Test Coverage details in HP-ALM using Requirement Details

Question

I am running SQL query in HP-ALM to fetch Test Coverage Details using Requirements Entity For Example

SELECT REQ.RQ_REQ_ID
FROM REQ

But I don't see any column related to Test Coverage in Requirement Entity. Similarly In Test Entity There is no associated Requirement ID How can I fetch Test Coverage details and Requirements details by joining them ?

SELECT REQ.RQ_REQ_ID, TEST.TS_TEST_ID
FROM REQ, TEST
WHERE TEST.TS_USER_TEMPLATE_04 = REQ.RQ_USER_TEMPLATE_18

Answer 1

Four Tables has be queried by SQL-JOIN

SELECT
 "requirement"."RQ_USER_TEMPLATE_20" AS "Parent Req ID",
 "requirement"."RQ_REQ_ID" AS "Req ID",
 "test"."TS_TEST_ID" AS "Test ID",
 "REL_NAME" AS "Target Release",
 "requirement"."RQ_REQ_AUTHOR" AS "Author",
 "requirement"."RQ_REQ_NAME" AS "Req Name",
 "test"."TS_NAME" AS "Test Name"
FROM
 TEST "test"
 INNER JOIN REQ_COVER "requirement_coverage" 
 ON 
 "test"."TS_TEST_ID" = "requirement_coverage"."RC_ENTITY_ID" 
 AND
 "requirement_coverage"."RC_ENTITY_TYPE" = 'TEST'
 INNER JOIN REQ "requirement" 
 ON 
 "requirement"."RQ_REQ_ID" = "requirement_coverage"."RC_REQ_ID"
 INNER JOIN ALL_LISTS "test_folder" 
 ON 
 "test_folder"."AL_ITEM_ID" = "test"."TS_SUBJECT"
 INNER JOIN REQ_TYPE "requirement_type" 
 ON 
 "requirement"."RQ_TYPE_ID" = "requirement_type"."TPR_TYPE_ID"

More details ALM Query Details