| || |
What is wrong with this SQL? Test cases & Defects
I am trying to pull following fields from the Test Lab using Excel Report Generator:
Test Case Name, Execution Status, Responsible Tester, Associated Defect ID, Priority, Status, and Defect Assigned To.
When I run following query, it pulls all test cases and shows one defect for all test cases. Then lists all test cases and shows next defect for all test cases and so on.
SELECT CY_CYCLE, TS_NAME, TC_STATUS, TC_TESTER_NAME, BG_BUG_ID, BG_PRIORITY, BG_STATUS, BG_RESPONSIBLE
FROM BUG, LINK, CYCLE, TEST, TESTCYCL
WHERE TESTCYCL.TC_TEST_ID =* TEST.TS_TEST_ID and
TESTCYCL.TC_CYCLE_ID =* CYCLE.CY_CYCLE_ID AND
TESTCYCL.TC_TESTCYCL_ID =* LINK.LN_ENTITY_ID AND
LINK.LN_BUG_ID = BUG.BG_BUG_ID
Another version of the query pulls all test cases and corresponding defects but I want all test cases to appear regardless if there is an associated defect or not but this one only shows test cases that have an associated defect. This version is below:
SELECT (SELECT TEST.TS_NAME FROM TEST WHERE TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID), TESTCYCL.TC_STATUS, TESTCYCL.TC_TESTER_NAME, (SELECT BUG.BG_USER_02 FROM BUG WHERE BUG.BG_BUG_ID = LINK.LN_BUG_ID), (SELECT BUG.BG_BUG_ID FROM BUG WHERE BUG.BG_BUG_ID = LINK.LN_BUG_ID), (SELECT BUG.BG_SEVERITY FROM BUG WHERE BUG.BG_BUG_ID = LINK.LN_BUG_ID), (SELECT BUG.BG_USER_03 FROM BUG WHERE BUG.BG_BUG_ID = LINK.LN_BUG_ID), (SELECT BUG.BG_RESPONSIBLE FROM BUG WHERE BUG.BG_BUG_ID = LINK.LN_BUG_ID)
FROM TESTCYCL INNER JOIN LINK ON (TESTCYCL.TC_TESTCYCL_ID = LINK.LN_ENTITY_ID)
WHERE LINK.LN_ENTITY_TYPE = 'TESTCYCL' AND TESTCYCL.TC_STATUS <> 'Passed'
Can some SQL guru help me?
Thanks in advance!
Re: What is wrong with this SQL? Test cases & Defects
You will need to use a LEFT JOIN to get all the contents of a table even when there is no matching data in the second table.