Hi,
I have a problem with my sql query.
It contains three tables. TestPlanNodes, ManualSteps and ManualTestStepResults.
This db saves data from diffrent test rounds.
ManualSteps contains all different test steps that can be made and ManualTestStepResults contains the results from all the test rounds (one test step can have many results).
As the query is below it shows all results for one test step, but I just want to show the latest result.
I put ### around the text in the query where I think the query should be changed.
SQL Query:
SELECT td.TestContainerName, td.TestID, td.TestName, td.TestDescription, td.TreeOrder, tpn.parentNodeId_fk as ParentNode, tpn.OrderNumber, tpn1.name as ParentName, tpn1.Description as ParentDesc, tpn1.OrderNumber as ParentOrderNum, tpn2.nodeId_pk as GrandParentId, tpn2.name as GrandParentName, tpn2.Description as GrandParentDesc, tpn2.OrderNumber as GPOrderNum, tpn3.nodeId_pk as GGParentId, tpn3.name as GGParentName, tpn3.Description as GGPDesc, tpn3.OrderNumber as GGPOrderNum, ms.ManualDefinitionId_fk as ID, ms.Name as StepName, ms.SequenceNumber as StepNum, ms.Description as StepDetail, ms.Result as ExpRes, sr.Result as Result, sr.Status as Status
FROM RTM_V_TestDefinitions td
INNER JOIN TM_TestPlanNodes tpn ON (td.TestID = tpn.nodeId_pk)
FULL OUTER JOIN TM_TestPlanNodes tpn1 ON (tpn.parentNodeId_fk = tpn1.nodeId_pk)
FULL OUTER JOIN TM_TestPlanNodes tpn2 ON (tpn1.parentNodeId_fk = tpn2.nodeId_pk)
FULL OUTER JOIN TM_TestPlanNodes tpn3 ON (tpn2.parentNodeId_fk = tpn3.nodeId_pk)
LEFT OUTER JOIN TM_ManualSteps ms ON (ms.ManualDefinitionId_fk = td.TestID)
###
FULL OUTER JOIN TM_ManualTestStepResults sr ON (ms.StepID_pk = sr.StepID_fk)
###
WHERE (td.ProjectID = ${$PROJECTID})
ORDER BY td.TreeOrder ASC
I have a problem with my sql query.
It contains three tables. TestPlanNodes, ManualSteps and ManualTestStepResults.
This db saves data from diffrent test rounds.
ManualSteps contains all different test steps that can be made and ManualTestStepResults contains the results from all the test rounds (one test step can have many results).
As the query is below it shows all results for one test step, but I just want to show the latest result.
I put ### around the text in the query where I think the query should be changed.
SQL Query:
SELECT td.TestContainerName, td.TestID, td.TestName, td.TestDescription, td.TreeOrder, tpn.parentNodeId_fk as ParentNode, tpn.OrderNumber, tpn1.name as ParentName, tpn1.Description as ParentDesc, tpn1.OrderNumber as ParentOrderNum, tpn2.nodeId_pk as GrandParentId, tpn2.name as GrandParentName, tpn2.Description as GrandParentDesc, tpn2.OrderNumber as GPOrderNum, tpn3.nodeId_pk as GGParentId, tpn3.name as GGParentName, tpn3.Description as GGPDesc, tpn3.OrderNumber as GGPOrderNum, ms.ManualDefinitionId_fk as ID, ms.Name as StepName, ms.SequenceNumber as StepNum, ms.Description as StepDetail, ms.Result as ExpRes, sr.Result as Result, sr.Status as Status
FROM RTM_V_TestDefinitions td
INNER JOIN TM_TestPlanNodes tpn ON (td.TestID = tpn.nodeId_pk)
FULL OUTER JOIN TM_TestPlanNodes tpn1 ON (tpn.parentNodeId_fk = tpn1.nodeId_pk)
FULL OUTER JOIN TM_TestPlanNodes tpn2 ON (tpn1.parentNodeId_fk = tpn2.nodeId_pk)
FULL OUTER JOIN TM_TestPlanNodes tpn3 ON (tpn2.parentNodeId_fk = tpn3.nodeId_pk)
LEFT OUTER JOIN TM_ManualSteps ms ON (ms.ManualDefinitionId_fk = td.TestID)
###
FULL OUTER JOIN TM_ManualTestStepResults sr ON (ms.StepID_pk = sr.StepID_fk)
###
WHERE (td.ProjectID = ${$PROJECTID})
ORDER BY td.TreeOrder ASC
