mpardy
asked on
Subquery returned more then one result. Pull vals from a look up table into comma delimited list
I am trying to run a subquery that goes to a look table and pulls back multiple vals that I am putting into a comma delimited list and storing in one var. I am getting th Subquery returned more than 1 result error msg. But when I run the sub query on its own it just returns one result. This is the query:
SELECT MJ_WELL.API, MJ_WELL.SPUD_DATE, MJ_WELL.WELL_STATUS, MJ_WELL.LATITUDE, MJ_WELL.LONGITUDE, MJ_WELL.MJW_ID,
MJ_WELL.PROVINCE_ID, + '1' + MJ_LOCATION_DLS.LOCATION_E
MJ_LOCATION_DLS.SECTION) + '-' + CONVERT(nvarchar, MJ_LOCATION_DLS.TOWNSHIP) + '-' + CONVERT(nvarchar, MJ_LOCATION_DLS.RANGE)
+ '' + CONVERT(nvarchar, MJ_LOCATION_DLS.MERIDIAN_D
MJ_LOCATION_DLS.EVENT_SEQ)
(SELECT COALESCE (MJ_LOG_TYPE.NAME + ', ', '') + CAST(MJ_LOG_TYPE.NAME AS varchar(100)) AS LOGS
FROM MJ_LOG_TYPE INNER JOIN
MJ_LOG ON MJ_LOG.LOG_TYPE_ID = MJ_LOG_TYPE.LOG_TYPE_ID INNER JOIN
MJ_WELL AS MJ_WELL_1 ON MJ_WELL.MJW_ID = MJ_LOG.MJW_ID
WHERE (MJ_WELL.MJW_ID = 1)) AS Expr1
FROM MJ_WELL AS MJ_WELL INNER JOIN
MJ_LOCATION_DLS ON MJ_WELL.MJW_ID = MJ_LOCATION_DLS.MJW_ID
SELECT MJ_WELL.API, MJ_WELL.SPUD_DATE, MJ_WELL.WELL_STATUS, MJ_WELL.LATITUDE, MJ_WELL.LONGITUDE, MJ_WELL.MJW_ID,
MJ_WELL.PROVINCE_ID, + '1' + MJ_LOCATION_DLS.LOCATION_E
MJ_LOCATION_DLS.SECTION) + '-' + CONVERT(nvarchar, MJ_LOCATION_DLS.TOWNSHIP) + '-' + CONVERT(nvarchar, MJ_LOCATION_DLS.RANGE)
+ '' + CONVERT(nvarchar, MJ_LOCATION_DLS.MERIDIAN_D
MJ_LOCATION_DLS.EVENT_SEQ)
(SELECT COALESCE (MJ_LOG_TYPE.NAME + ', ', '') + CAST(MJ_LOG_TYPE.NAME AS varchar(100)) AS LOGS
FROM MJ_LOG_TYPE INNER JOIN
MJ_LOG ON MJ_LOG.LOG_TYPE_ID = MJ_LOG_TYPE.LOG_TYPE_ID INNER JOIN
MJ_WELL AS MJ_WELL_1 ON MJ_WELL.MJW_ID = MJ_LOG.MJW_ID
WHERE (MJ_WELL.MJW_ID = 1)) AS Expr1
FROM MJ_WELL AS MJ_WELL INNER JOIN
MJ_LOCATION_DLS ON MJ_WELL.MJW_ID = MJ_LOCATION_DLS.MJW_ID
er, not inner join but I meant to say sub query tables a different alias.
>pulls back multiple vals that I am putting into a comma delimited list and storing in one var
No, that is not what you are doing. You are pulling back multiple values with a comma appended. You are no longer appending the value of the first row returned to the value of the second row returned to the value of the third, etc. to build a delimited list in one variable.
With that said, I don't have an immediate solution...will think
No, that is not what you are doing. You are pulling back multiple values with a comma appended. You are no longer appending the value of the first row returned to the value of the second row returned to the value of the third, etc. to build a delimited list in one variable.
With that said, I don't have an immediate solution...will think
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ooops...
Forgot this as the last line of the function:
Return @ans
Forgot this as the last line of the function:
Return @ans
I think your query is possibly trying to correlate to the outer query.
Just a thought.