Subquery - SQL Server Forums

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

Subquery

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2013-01-14 : 12:04:47

Struggling with this a bit ..

There is a RELEASES table with a column REL_ID which is the key. Also in the RELEASES table is a column REL_NAME. REL_NAME is the piece of data I need selected by a sub-query.

There is a table RELEASE_CYCLES with a column RCYC_ID which is the key. Also in the RELEASE_CYCLES table is a column REL_PARENT_ID which relates to the REL_ID in the RELEASES table.

There is a table CYCLES with a column CY_ASSIGN_RCYC which relates to RCYC_ID in the RELEASE_CYCLES.

Struggling a bit to write this sub-query. This is what I'm trying now and can't get past error:

  
 (
  SELECT REL.REL_NAME
  FROM RELEASES
  JOIN RELEASE_CYCLES RCY_CY ON RCY_CY.RCYC_ID = CY_ASSIGN_RCYC
  JOIN RELEASES REL ON REL.REL_ID = RCY_CY.RCYC_PARENT_ID
  ) AS "Test Set Assigned Release",

With this, I get "Subquery returned more than 1 value" error. Not sure where the problem is but there should only be 1 release name returned.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-14 : 12:18:22

The subquery is returning more than one row, and you are using it in a context where only one row should be returned. Perhaps you need to correlate it with the outer query so only one row will be returned, or may be use an aggregate function such as MAX to return only one row?

(
  SELECT REL.REL_NAME
  FROM RELEASES
  JOIN RELEASE_CYCLES RCY_CY ON RCY_CY.RCYC_ID = CY_ASSIGN_RCYC
  JOIN RELEASES REL ON REL.REL_ID = RCY_CY.RCYC_PARENT_ID
  WHERE REL.REL_ID = OUTERTABLE.REL_ID
  ) AS "Test Set Assigned Release",

Or if it indeed should return multiple rows, you need to make it into a virtual table and join with the outer table?


  ....
  s.REL_NAME as "Test Set Assigned Release"
  ...
  FROM
     OUTERTABLE o
     INNER JOIN
     (
	   SELECT REL.REL_NAME, REL.SOME_JOIN_COLUMN
	   FROM RELEASES
	   JOIN RELEASE_CYCLES RCY_CY ON RCY_CY.RCYC_ID = CY_ASSIGN_RCYC
	   JOIN RELEASES REL ON REL.REL_ID = RCY_CY.RCYC_PARENT_ID
	   WHERE REL.REL_ID = OUTERTABLE.REL_ID
     ) AS s on S.SOME_JOIN_COLUMN = OUTERTABLE.SOMEJOINCOLUMN
 ....

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2013-01-14 : 13:27:11

There will always be only 1 release name associated with a release_cycle and only one release cycle associated with a cycle. So the query should never return but 1 row. Since its returning multiple rows, limiting it to returning 1 row could be the wrong row. So the problem has to be, I need to "correlate it with the outer query" as you say. I tried the WHERE clause you suggested but it tells me "mutli part identifier OUTERTABLE.REL_ID can't be bound". I'm lost, sorry.

Maybe if I post the entire query that would help?


SELECT

  CYCLE.CY_CYCLE_ID AS "Test Set ID",
  CYCLE.CY_CYCLE AS "Test Set Name",
  CYCLE.CY_USER_07 AS "Test Set Folder",

  -- RELEASE NAME:
  (
  SELECT REL.REL_NAME
  FROM RELEASES
  JOIN RELEASE_CYCLES RCY_CY ON RCY_CY.RCYC_ID = CY_ASSIGN_RCYC
  JOIN RELEASES REL ON REL.REL_ID = RCY_CY.RCYC_PARENT_ID
  ) AS "Test Set Assigned Release",

  TEST.TS_USER_04 AS "Test Functional Area",
  TEST.TS_TEST_ID AS "Test ID",
  TEST.TS_NAME AS "Test Name"

FROM REQ

  JOIN REQ_COVER ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
  RIGHT JOIN TEST ON TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
  JOIN TESTCYCL ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
  JOIN CYCLE ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
  JOIN CYCL_FOLD ON CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID

WHERE REQ.RQ_REQ_ID IS NULL

ORDER BY CYCLE.CY_CYCLE_ID

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2013-01-14 : 13:48:39

This WHERE clause in the subquery solved the issue:

WHERE CYCLE.CY_ASSIGN_RCYC = RCY_CY.RCYC_ID AND RCY_CY.RCYC_PARENT_ID = RELEASES.REL_ID

Thanks for pointing me in the right direction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources