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.
Author |
Topic |
planetoneautomation
Posting Yak Master
105 Posts |
Posted - 2012-11-13 : 09:42:13
|
The following query returns 261 rows when I expect 3 rows. In this SQL, the "Test Set" contains three "Tests". The query returns 87 rows per test (261/3 = 87).I tried using DISTINCT as I saw it used in some examples but it gives me a syntax error. I think DISTINCT is not what I need anyway.Here's the SQL ... any guidance? ...SELECT CYCLE.CY_CYCLE_ID AS "Test Set ID", CYCLE.CY_CYCLE AS "Test Set Name", CYCLE.CY_USER_07 AS "Test Set Folder", TEST.TS_USER_04 AS "Test Functional Area", TEST.TS_TEST_ID AS "Test ID", TEST.TS_NAME AS "Test Name", REQ.RQ_REQ_ID AS "Req ID", REQ.RQ_REQ_NAME AS "Req Name", REQ.RQ_USER_25 AS "Req Folder Path"FROM REQ, REQ_COVER, TEST, TESTCYCL, CYCLE, CYCL_FOLDWHERE cycle.cy_cycle_id = 1261 and REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID AND TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID AND TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID AND TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID AND REQ.RQ_REQ_ID IS NOT NULLORDER BY CYCLE.CY_CYCLE_ID |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-13 : 09:49:19
|
You're mising a join. Fill that in and see if that clears things upSELECT CYCLE.CY_CYCLE_ID AS "Test Set ID", CYCLE.CY_CYCLE AS "Test Set Name", CYCLE.CY_USER_07 AS "Test Set Folder", TEST.TS_USER_04 AS "Test Functional Area", TEST.TS_TEST_ID AS "Test ID", TEST.TS_NAME AS "Test Name", REQ.RQ_REQ_ID AS "Req ID", REQ.RQ_REQ_NAME AS "Req Name", REQ.RQ_USER_25 AS "Req Folder Path"FROM REQ INNER JOIN REQ_COVER ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_IDINNER JOIN TEST ON TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_IDINNER JOIN TESTCYCL ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_IDINNER JOIN CYCLE ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_IDINNER JOIN CYCL_FOLD ON WHERE cycle.cy_cycle_id = 1261 AND REQ.RQ_REQ_ID IS NOT NULLORDER BY CYCLE.CY_CYCLE_IDJimEveryday I learn something that somebody else already knew |
|
|
planetoneautomation
Posting Yak Master
105 Posts |
Posted - 2012-11-13 : 10:09:48
|
Yep, that did it:INNER JOIN CYCL_FOLD ON CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID Thanks. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-13 : 10:43:17
|
You're welcome. Using the ANSI join standard makes things like this easier to spot.JimEveryday I learn something that somebody else already knew |
|
|
planetoneautomation
Posting Yak Master
105 Posts |
Posted - 2012-11-13 : 14:23:33
|
In the SQL below, a single row is returned for each "Test Set" which is correct. I'm using COUNT to count the number of "Tests" in the "Test Set", in this case three "Tests" and that's what the query WAS returning ... before I added the 2nd and 3rd JOIN statements which were needed to count "Steps". A "Test" has one or more "Steps". I'm now trying to count the number of "Steps" for a "Test".So, for example, I know that a particular "Test Set" has three tests. One of those three tests has 3 steps, one has 2 steps and one has 1 step for a total of (3+2+1=) 6 steps.The SQL below returns the correct number of steps (6) but now, instead of 3 tests its reporting 6 steps. The number of steps is always correct but the number of tests now always equals the number of steps.What's wrong? I've tried every combination of INNER,OUTER,LEFT,RIGHT JOIN there is and not matter what, the count of tests always matches the count of steps.-- Test Set ReportSELECT-- Test Set FieldsCYCLE.CY_CYCLE AS "Test Set",--QC Memo fields must be converted to nvarcharconvert(nvarchar(MAX),CYCLE.CY_COMMENT) AS "Description",CYCLE.CY_USER_01 AS "Functional Area",CYCLE.CY_OPEN_DATE AS "Open Date",CYCLE.CY_CLOSE_DATE AS "Close Date",CYCLE.CY_REQUEST_ID AS "ITG Request ID",CYCLE.CY_STATUS AS "Status",CYCLE.CY_PINNED_BASELINE AS "Baseline",CYCLE.CY_CYCLE_ID AS "Cycle ID",CYCLE.CY_ASSIGN_RCYC AS "Target Cycle",CYCLE.CY_VTS AS "Modified",-- Test Instance Fields & Step FieldsCOUNT(TESTCYCL.TC_TEST_ID) AS "Number of Test Instances",COUNT(STEP.ST_ID) AS "Number of Steps"FROM TESTCYCLINNER JOIN CYCLE ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_IDJOIN RUN ON RN_TESTCYCL_ID = TC_TESTCYCL_IDJOIN STEP ON ST_RUN_ID = RN_RUN_IDGROUP BY CYCLE.CY_CYCLE, CAST (CYCLE.CY_COMMENT AS NVARCHAR(MAX)), --QC Memo fields must be converted to nvarcharCYCLE.CY_USER_01, CYCLE.CY_OPEN_DATE, CYCLE.CY_CLOSE_DATE, CYCLE.CY_REQUEST_ID,CYCLE.CY_STATUS, CYCLE.CY_PINNED_BASELINE, CYCLE.CY_CYCLE_ID, CYCLE.CY_ASSIGN_RCYC, CYCLE.CY_VTSORDER BY CYCLE.CY_CYCLE_ID |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-13 : 14:47:07
|
TryCOUNT(DISTINCT TESTCYCL.TC_TEST_ID) AS "Number of Test Instances",JimEveryday I learn something that somebody else already knew |
|
|
planetoneautomation
Posting Yak Master
105 Posts |
Posted - 2012-11-13 : 14:52:11
|
DISTINCT worked. Why does the answer always seem obvious once I know it? At some point, this stuff has to sink in. I could write procedural code (C##, whatever) all day long to get what I need using simple SELECT statements and spinning through record sets but anything more complicated than a straight SELECT and maybe a JOIN or two sends me reeling. I guess its a matter of doing it enough times. Thanks again. |
|
|
|
|
|
|
|