SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Too Many Rows?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

planetoneautomation
Yak Posting Veteran

90 Posts

Posted - 11/13/2012 :  09:42:13  Show Profile  Reply with Quote
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_FOLD

WHERE
  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 NULL

ORDER BY CYCLE.CY_CYCLE_ID

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/13/2012 :  09:49:19  Show Profile  Reply with Quote
You're mising a join. Fill that in and see if that clears things up
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
INNER JOIN REQ_COVER ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
INNER JOIN TEST ON TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
INNER JOIN TESTCYCL ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
INNER JOIN CYCLE ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
INNER JOIN CYCL_FOLD ON

WHERE
cycle.cy_cycle_id = 1261
AND REQ.RQ_REQ_ID IS NOT NULL

ORDER BY CYCLE.CY_CYCLE_ID

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

planetoneautomation
Yak Posting Veteran

90 Posts

Posted - 11/13/2012 :  10:09:48  Show Profile  Reply with Quote
Yep, that did it:


INNER JOIN CYCL_FOLD ON CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID


Thanks.

Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/13/2012 :  10:43:17  Show Profile  Reply with Quote
You're welcome. Using the ANSI join standard makes things like this easier to spot.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

planetoneautomation
Yak Posting Veteran

90 Posts

Posted - 11/13/2012 :  14:23:33  Show Profile  Reply with Quote
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 Report

SELECT

-- Test Set Fields
CYCLE.CY_CYCLE AS "Test Set",
--QC Memo fields must be converted to nvarchar
convert(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 Fields
COUNT(TESTCYCL.TC_TEST_ID) AS "Number of Test Instances",
COUNT(STEP.ST_ID) AS "Number of Steps"

FROM TESTCYCL

INNER JOIN CYCLE ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
JOIN RUN ON RN_TESTCYCL_ID = TC_TESTCYCL_ID
JOIN STEP ON ST_RUN_ID = RN_RUN_ID

GROUP BY CYCLE.CY_CYCLE, CAST (CYCLE.CY_COMMENT AS NVARCHAR(MAX)), --QC Memo fields must be converted to nvarchar
CYCLE.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_VTS

ORDER BY CYCLE.CY_CYCLE_ID
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/13/2012 :  14:47:07  Show Profile  Reply with Quote
Try
COUNT(DISTINCT TESTCYCL.TC_TEST_ID) AS "Number of Test Instances",


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

planetoneautomation
Yak Posting Veteran

90 Posts

Posted - 11/13/2012 :  14:52:11  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000