I need to get all test cases of an Azure DevOps iterationPath along with their results (passed, failed, etc).
I tried it in several ways, including doing inner join between 3 tables (from the azure API), but with no success as duplicate data comes.
Tables used: Testpoints, Workitems, Iterations
Query:
SELECT distinct w.WorkItemId, t.TestCaseId, t.LastResultOutcome, w.WorkItemType, i.IterationPath, i.IterationId, i.IterationName, i.IterationLevel1, i.IterationLevel2,i.IterationLevel3 as "Suite de Teste"
FROM vsts_iterations i
inner join vsts_workitems w On i.IterationSK = w.IterationSK
inner join vsts_testpoints t On w.ProjectSk = t.ProjectSk
where i.IterationSK like '%04b04041-e5ff-4ace-9ed5-1fdf55bf403d%'
and
w.WorkItemType = 'Test Case'
order by w.WorkItemId asc;
Expected outcome: