[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

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_EXC + CONVERT(nvarchar, MJ_LOCATION_DLS.LSD) + '/' + CONVERT(nvarchar,
                      MJ_LOCATION_DLS.SECTION) + '-' + CONVERT(nvarchar, MJ_LOCATION_DLS.TOWNSHIP) + '-' + CONVERT(nvarchar, MJ_LOCATION_DLS.RANGE)
                      + '' + CONVERT(nvarchar, MJ_LOCATION_DLS.MERIDIAN_DIR) + '' + CONVERT(nvarchar, MJ_LOCATION_DLS.MERIDIAN) + '/0' + CONVERT(nvarchar,
                      MJ_LOCATION_DLS.EVENT_SEQ) + '' AS LOCATION, MJ_WELL.KELLY_BUSHING, CONVERT(nvarchar, MJ_WELL.TOTAL_DEPTH) + ' m' AS TDMETERS,
                          (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
0
mpardy
Asked:
mpardy
  • 3
  • 2
1 Solution
 
ichthus1Commented:
Give your inner join of same tables a different alias.

I think your query is possibly trying to correlate to the outer query.

Just a thought.
0
 
ichthus1Commented:
er, not inner join but I meant to say sub query tables a different alias.
0
 
dqmqCommented:
>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
0
 
dqmqCommented:
OK, I remember...
You need to put your inner select inside a function.  something like:

Create Function YourFunction (@MJW_ID Integer)
(
Returns varchar(8000)
AS
Begin
  declare @ans varchar(8000)
  SELECT  @ans=COALESCE (@ans + ', ', '') + CAST(MJ_LOG_TYPE.NAME AS varchar(100))
        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 = (@MJW_ID ))

end


Then replace the subselect with a function call:

Select ...., dbo.yourfunction(1) from ...
0
 
dqmqCommented:
Ooops...
Forgot this as the last line of the function:

Return @ans
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now