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
 Subquery		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

planetoneautomation
Yak Posting Veteran

89 Posts
Posted - 01/14/2013 :  12:04:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1733 Posts
Posted - 01/14/2013 :  12:18:22  Show Profile  Reply with Quote
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
 ....
Go to Top of Page

planetoneautomation
Yak Posting Veteran

89 Posts
Posted - 01/14/2013 :  13:27:11  Show Profile  Reply with Quote
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
Go to Top of Page

planetoneautomation
Yak Posting Veteran

89 Posts
Posted - 01/14/2013 :  13:48:39  Show Profile  Reply with Quote
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.
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.09 seconds. Powered By: Snitz Forums 2000