Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SELECT - returns more rows than needed

Author  Topic 

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2013-01-17 : 14:33:04
The best way to describe this issue is to show the SQL and then to show the results and then the result I need.

SQL:


SELECT
TEST.TS_TEST_ID as 'Test Plan ID',
TEST.TS_NAME as 'Test Plan Name',
TEST.TS_STEPS as 'Test Plan Step Count',
TEST.TS_RESPONSIBLE AS 'Test Plan Designer',
TEST.TS_USER_03 AS 'Test Plan Business Value',
TEST.TS_USER_02 AS 'Test Plan Complexity',
TEST.TS_USER_04 AS 'Test Plan Functional Area',
TEST.TS_USER_25 as 'Test Plan Full Folder Path',
RUN.RN_RUN_ID as 'Run ID',
RUN.RN_RUN_NAME as 'Run Name',
RUN.RN_STATUS as 'Run Status',
RUN.RN_DURATION AS "Run Duration",
RUN.RN_TESTER_NAME as 'Run Tester ID',
RUN.RN_ASSIGN_RCYC AS "Run Target Cycle",
RCY_RN.RCYC_NAME AS "Run Target Cycle Name",
TESTCYCL.TC_EXEC_DATE AS "Instance Execution Date",
TESTCYCL.TC_EXEC_TIME AS "Instance Execution Time",
TESTCYCL.TC_PLAN_SCHEDULING_DATE AS "Instance Planned Exec Date",
TESTCYCL.TC_PLAN_SCHEDULING_TIME AS "Instance Planned Exec Time",
TESTCYCL.TC_ASSIGN_RCYC as 'Instance Assigned Cycle',
RCY_TC.RCYC_NAME AS "Instance Cycle Name",
TESTCYCL.TC_USER_01 as 'Instance Responsible Tester Name',
TESTCYCL.TC_USER_02 as 'Instance Planned End Date',
CYCLE.CY_CYCLE AS "Test Set Name",
CYCLE.CY_CYCLE_ID AS "Test Set ID",
CYCLE.CY_USER_01 AS "Test Set Functional Area",
CONVERT(NVARCHAR(MAX),CYCLE.CY_COMMENT) AS "Test Set Description", --QC Memo fields must be converted to nvarchar
CYCLE.CY_ASSIGN_RCYC AS "Test Set Target Cycle ID" ,
RCY_CY.RCYC_NAME AS "Test Set Target Cycle Name",
CONVERT(NVARCHAR(MAX),CYCLE.CY_USER_07) AS "Test Set Full Folder Path", --QC Memo fields must be converted to nvarchar
DESSTEPS.DS_STEP_NAME as 'Step Name',
STEP.ST_STATUS as 'Step Status' ,
DESSTEPS.DS_ID as 'Step ID',
STEP.ST_EXECUTION_DATE as 'Step Execution Start Date',
DESSTEPS.DS_DESCRIPTION as 'Step Description',
DESSTEPS.DS_USER_01 as 'Step Test Data',
DESSTEPS.DS_USER_02 AS 'Step Audit Evidence(DESC)',
DESSTEPS.DS_USER_03 as 'Step Audit Evidence Required',
DESSTEPS.DS_USER_04 AS 'Step Audit Verification Type',
DESSTEPS.DS_USER_05 as 'Step Audit Approve/Reject',
DESSTEPS.DS_USER_06 as 'Step Control Point ID(s)',
STEP.ST_EXECUTION_TIME as 'Step Execution Time',
STEP.ST_ACTUAL as 'Step Actual Result'
FROM CYCL_FOLD LEFT JOIN CYCLE ON CYCL_FOLD.CF_ITEM_ID = CYCLE.CY_FOLDER_ID
LEFT JOIN TESTCYCL ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
LEFT JOIN TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
LEFT JOIN RUN ON TEST.TS_TEST_ID = RUN.RN_TEST_ID AND RN_EXECUTION_DATE = (SELECT MAX(RN_EXECUTION_DATE) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID) AND RN_EXECUTION_TIME = (SELECT MAX(RN_EXECUTION_TIME) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID AND RN_EXECUTION_DATE = (SELECT MAX(RN_EXECUTION_DATE) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID))
LEFT JOIN RELEASE_CYCLES RCY_RN ON RCY_RN.RCYC_ID = RN_ASSIGN_RCYC
LEFT JOIN RELEASE_CYCLES RCY_CY ON RCY_CY.RCYC_ID = CY_ASSIGN_RCYC
LEFT JOIN RELEASE_CYCLES RCY_TC ON RCY_TC.RCYC_ID = TC_ASSIGN_RCYC
LEFT JOIN STEP ON RUN.RN_RUN_ID = STEP.ST_RUN_ID
LEFT JOIN DESSTEPS ON DESSTEPS.DS_TEST_ID = TEST.TS_TEST_ID
WHERE TESTCYCL.TC_ASSIGN_RCYC = @Assigned_Cycle@ AND TS_TYPE = 'MANUAL'
ORDER BY TEST.TS_TEST_ID


Actual Results: (Note - not showing all columns - just enough to illustrate:

Step Name Step Status
Step 1
Step 2
Step 1 Passed
Step 2 Passed
Step 1 Failed
Step 2 Failed
Step 1
Step 2

Expected Results:

Step Name Step Status
Step 1
Step 2
Step 1 Passed
Step 2 Failed
Step 1
Step 2

Any idea? I've tried several things with the JOIN statements but I'm unable to figure out what to change to achieve this.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-17 : 17:51:43
Hard to say, probalby a data/design issue. You might try adding a DISTINCT to your SELECT statement and see if that solves the issue. If not, see the following links:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-17 : 22:39:44
tell us rules by means of which you want output to come


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2013-01-21 : 10:06:10
Using DISTINCT does not change the output.

There will always be rows in DESSTEPS but there may or may not be rows in STEP. A returned row will have values for the STEP table columns if there are rows in the STEP table, otherwise those STEP columns will be empty. There will always be DESSTEPS rows and the data in the DESSTEPS columns will be selected.

The problem is, there are multiple rows selected if there is data in both STEP and DESSTEPS.

There's got to be a way to do this, right?

Not sure I understand Lamprey's post that its "probaly a data/design issue". If that means, the schema is not properly designed and as a result I can't do what I want to do then I'm in trouble as this is a 3rd party DB and the schema cannot be changed.


Go to Top of Page

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2013-01-21 : 10:40:49
Maybe this will help. I've removed much of the query that was irrelevant to the issue leaving only what's necessary to reproduce the problem.


SELECT
TEST.TS_TEST_ID as 'Test Plan ID',
RUN.RN_RUN_ID as 'Run ID',
CYCLE.CY_CYCLE_ID AS "Test Set ID",
DESSTEPS.DS_STEP_NAME as 'Step Name',
STEP.ST_STATUS as 'Step Status' ,
DESSTEPS.DS_ID as 'Step ID'
FROM CYCL_FOLD
LEFT JOIN CYCLE ON CYCL_FOLD.CF_ITEM_ID = CYCLE.CY_FOLDER_ID
LEFT JOIN TESTCYCL ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
LEFT JOIN TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
LEFT JOIN RUN ON TEST.TS_TEST_ID = RUN.RN_TEST_ID AND RN_EXECUTION_DATE = (SELECT MAX(RN_EXECUTION_DATE) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID) AND RN_EXECUTION_TIME = (SELECT MAX(RN_EXECUTION_TIME) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID AND RN_EXECUTION_DATE = (SELECT MAX(RN_EXECUTION_DATE) FROM RUN WHERE TC_TESTCYCL_ID = RN_TESTCYCL_ID))
LEFT JOIN STEP ON RUN.RN_RUN_ID = STEP.ST_RUN_ID
LEFT JOIN DESSTEPS ON DESSTEPS.DS_TEST_ID = TEST.TS_TEST_ID
WHERE TESTCYCL.TC_ASSIGN_RCYC = @Assigned_Cycle@ AND TS_TYPE = 'MANUAL'
ORDER BY TEST.TS_TEST_ID


And, once again, here's the actual versus expecting result ...

Actual Results: (Note - not showing all columns - just enough to illustrate:

Step Name Step Status
Step 1
Step 2
Step 1 Passed
Step 2 Passed
Step 1 Failed
Step 2 Failed
Step 1
Step 2

Expected Results:

Step Name Step Status
Step 1
Step 2
Step 1 Passed
Step 2 Failed
Step 1
Step 2

Any ideas are greatly appreciated. Thanks.
Go to Top of Page
   

- Advertisement -