0

I am trying to get the linked test cases associated to a defect. The Problem arises when i run the query, i am getting duplicate values even when there is a "DISTINCT" clause to the query.

Select Distinct  LINK.LN_ENTITY_ID as "Link_Id",
Bg_Bug_ID as "Defect_ID",
Bg_Status as "Defect_Status",
TC_STATUS as "TestCase_status",
TS_NAME as "TestCase_Name"

from (BUG LEFT JOIN LINK ON BG_BUG_ID = LN_BUG_ID )
LEFT JOIN V_LINK_TESTCYCL on LINK.LN_BUG_ID = V_LINK_TESTCYCL.LN_BUG_ID
LEFT JOIN CYCLE ON LINK.LN_ENTITY_ID = CY_CYCLE_ID
LEFT JOIN TESTCYCL on V_LINK_TESTCYCL.LN_TESTCYCL_ID = TC_TESTCYCL_ID
left join test on tc_test_id = ts_test_id

Where
--NOT IN ('Closed','Defect Resolved','Rejected')
BG_STATUS NOT IN ('Closed','Defect Resolved','Rejected')
--BG_STATUS = 'Awaiting Retest'
AND LINK.LN_ENTITY_TYPE = 'TESTCYCL'
AND TC_STATUS NOT IN ('Passed')
ORDER BY LINK.LN_ENTITY_ID
--Group by BG_BUG_ID,LINK.LN_LINK_ID LINK.LN_ENTITY_ID /*Associated.LinkedEntity ID*/

I am not good at SQL, So i would appreciate any kind of suggestion that i recieve.

System : HP ALM Database : SQL

Let me know if you need any more details.

EDITED : Hope this helps, If you can see, i have only two test cases linked to defect 95, but the query returns 2 result for each. So if there is 4 test cases linked to a defect, there will be around 16 Link_Id Defect_ID Defect_Status TestCase_status TestCase_Name 768 95 Awaiting Retest Failed TC_01_XXXXXXX 768 95 Awaiting Retest No Run TC02_XXXXXXXX 787 95 Awaiting Retest Failed TC_01_XXXXXXX 787 95 Awaiting Retest No Run TC02_XXXXXXXX

nishit dey
  • 458
  • 1
  • 7
  • 21
  • 1
    can you share a record sample of each table? – funcoding Nov 09 '17 at 13:26
  • 1
    update your question add a proper data sample .. your actual result and the expected result – ScaisEdge Nov 09 '17 at 13:26
  • 1
    i think you don't understand how DISTINCT works, DISTINCT works on all columns in the select.. – Raymond Nijland Nov 09 '17 at 13:27
  • @funcoding : Table's are very big with many rows. – nishit dey Nov 09 '17 at 13:28
  • It is very difficult to help you with the amount of info you provided. In order to provide a good answer you will need to give us more `meat`. – funcoding Nov 09 '17 at 13:29
  • due to use of unqualified columns names, we can only guess at what the query is doing. What is the line "LEFT JOIN CYCLE ON LINK.LN_ENTITY_ID = CY_CYCLE_ID" for? My guess is that CYCLE is not contributing any columns to the result, but is giving you duplicate rows. – Mic Nov 09 '17 at 13:32
  • @Mic Removed the line but still getting the same result. – nishit dey Nov 09 '17 at 13:36
  • I wonder if you mean `INNER JOIN` instead. `LEFT JOIN` returns all rows from the left table, even if there are no matches in the right table. – funcoding Nov 09 '17 at 13:38
  • I would go back to your query and do the select table by table until you find which table is doing the duplicate. – funcoding Nov 09 '17 at 13:40
  • So each `BUG` record needs to be in the `LINK` table. I think that should be an `inner join`. Again I'm guessing, 'cause don't really know the full relationship on the tables. – funcoding Nov 09 '17 at 13:43
  • @funcoding let me try again will let you know, if i get something – nishit dey Nov 09 '17 at 13:44
  • @nishitdey Let me know! – funcoding Nov 09 '17 at 13:47

0 Answers0