Library of WONDERS

Objective # 1: Filter all testcases under Globe Portal Capabilities testplan (testplan_id = ‘9007’) and include summary, steps and expected result in the query

SQL query:

SELECT * FROM testplan_tcversions LEFT JOIN tcversions ON tcversions.id = testplan_tcversions.tcversion_id

WHERE testplan_tcversions.testplan_id = '9007'

Problem: Testcases have does not indicate its component/testsuite (Contacts, Search, Widgets etc.)

Objective # 2: Include Component(from nodes_hierarchy table) for each testcase in the query

SQL query:

SELECT a.*,  b.*, c.parent_id, c.node_type FROM testplan_tcversions a LEFT JOIN tcversions b ON b.id = a.tcversion_id LEFT JOIN nodes_hierarchy c ON a.tcversion_id = c.id

WHERE testplan_tcversions.testplan_id = '9007'

Problem: parent_id for testcases with node_type = ‘4’ is not sufficient to get the testcase component/testsuite

e.g.

In our result query for id = ‘9068’, ‘9067’ will only give us the summary/title of the testcase but not its component/testsuite , our next objective is to get the parent_id of ‘9067’

Objective # 3: Include the parent_id of  each parent_id for all the testcases in the query. Just like the following

To get the parent_id for each parent_id from the nodes_hierarchy table, we create a subquery:

SELECT x.id, x.name, x.node_type_id, x.parent_id, y.parent_id as parent_id2

FROM nodes_hierarchy x left join nodes_hierarchy y ON x.parent_id = y.id

Then add our subquery to our major query in Objective # 1:

SELECT a.*, b.*, d.*
FROM testplan_tcversions a
LEFT JOIN tcversions b on a.tcversion_id = b.id
LEFT JOIN (<em>select x.id, x.name, x.node_type_id, x.parent_id, y.parent_id as parent_id2 from nodes_hierarchy x left join nodes_hierarchy y on x.parent_id = y.id</em>) d on a.tcversion_id = d.id
WHERE a.testplan_id = '9007'

😀 Hats off Kuya Ed!