We help IT Professionals succeed at work.

SQL Server 2019 return multiple records from Subquery.

Medium Priority
45 Views
Last Modified: 2020-02-19
Hello all;

The query I am working with, I need a subquery to send back multiple records.
I know I have done this before in the past, but cannot find the code or remember what was done.

This is my query. (Of course, it gives this error: Subquery returned more than 1 value.)

SELECT   

(SELECT     distinct(Artists.ArtistName)
FROM            Albums INNER JOIN
                         Artists ON Albums.ArtistID = Artists.ArtistID INNER JOIN
                         Tracks ON Albums.AlbumID = Tracks.AlbumID INNER JOIN
                         Studios ON Tracks.TrackID = Studios.TrackID
WHERE        (Studios.StudioID = Studio.StudioID)) as Artists,


Studio.StName, ArtistPics.PicsPath
FROM            ArtistPics RIGHT OUTER JOIN
                         Studio ON ArtistPics.StudioID = Studio.StudioID
WHERE        (Studio.StName = 'Studio_308')

Open in new window


I need to return all the ArtistName's with a comma between them.

Thanks for any assistance on this one.
Wayne
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

Seems like you can use STRING_AGG:

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15


If you need help with it, can you post sample data and expected results?

Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Author

Commented:

Removing what is not needed for this, to make it simpler.

This will return just the name of the Studio.

SELECT        StName 
FROM            Studio 
WHERE        (StName = 'Studio_308')

This returns the 2 bands that are associated with the Studio

SELECT     distinct(Artists.ArtistName) 
FROM            Albums INNER JOIN 
                         Artists ON Albums.ArtistID = Artists.ArtistID INNER JOIN 
                         Tracks ON Albums.AlbumID = Tracks.AlbumID INNER JOIN 
                         Studios ON Tracks.TrackID = Studios.TrackID 
WHERE        (Studios.StudioID = 1)


So, in a single Query, as this site has the potential to grow rather large, so, a single query against the database is better than multiple ones.


The output should be:
StName               ArtistName      
Studio_308        Band_1, Band_3, Band_50, etc...

The Query, of course, is much more data, but to get this part working, would be great.

And also, hope you are doing well Netminder, it has been a very long time.

Author, Web Developer
CERTIFIED EXPERT
Top Expert 2009
Commented:

I did it.
I had to use a
= any
It was a pain, but I figured it out.

So, without placing all the information here.
I will post an example instead.
I hope this is able to be followed.
Either way, I got it working.


SELECT       distinct(table1.col1), (table2.col2) 
FROM            table3 INNER JOIN
                         table2 ON table3.col2ID = table2.col2ID INNER JOIN
                         table5 ON table3.col3ID = table5.col3ID INNER JOIN
                         table1s ON table5.col4ID = table1s.col4ID INNER JOIN
                         table1 ON table1s.col5ID = table1.col5ID
where table2.col2  = any
(SELECT     distinct(table2.col2)
FROM            table3 INNER JOIN
                         table2 ON table3.col2ID = table2.col2ID INNER JOIN
                         table5 ON table3.col3ID = table5.col3ID INNER JOIN
                         table1s ON table5.col4ID = table1s.col4ID
WHERE        table1s.col4ID = 1)


Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Author

Commented:

Since it did not take properly.
Here is the code again.


I did it.
I had to use a
= any
It was a pain, but I figured it out.

So, without placing all the information here.
I will post an example instead.
I hope this is able to be followed.
Either way, I got it working.


SELECT       distinct(table1.col1), (table2.col2) 

FROM            table3 INNER JOIN
                         table2 ON table3.col2ID = table2.col2ID INNER JOIN
                         table5 ON table3.col3ID = table5.col3ID INNER JOIN
                         table1s ON table5.col4ID = table1s.col4ID INNER JOIN
                         table1 ON table1s.col5ID = table1.col5ID
where table2.col2  = any
(SELECT     distinct(table2.col2)
FROM            table3 INNER JOIN
                         table2 ON table3.col2ID = table2.col2ID INNER JOIN
                         table5 ON table3.col3ID = table5.col3ID INNER JOIN
                         table1s ON table5.col4ID = table1s.col4ID
WHERE        table1s.col4ID = 1)


CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:

Seems inefficient but as long as you are happy with it.  To create the CSV, look at string_agg.  Without sample data and expected results I'm afraid I cannot help more.


>> hope you are doing well Netminder, it has been a very long time.


Not Netminder.  He is still around from time to time and doing well the last time we chatted.


My screen name is in honor of his brutal departure:   https://en.wiktionary.org/wiki/%E4%84%86

Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Author

Commented:

I am taking another look at the string_agg.
Through this page here. Concatenate multiple rows from a subquery into a string in SQL Server
The top code is what I was using, which uses the STUFF. I had wondered where I got it from, and now I know.

I will try and see what I can come up with, and I will post my results later on.
And thank you for the correction on the mistaken identity.

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

If you would like some assistance, give me a test case with sample data and expected results.

Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009

Author

Commented:

I was able to do it with the STUFF, and I got the results I needed.
Now, looking at the string_agg
I like this function. I will use it in another coding, but at the moment, I am going to move on, as I need to get this part of the site done. And hopefully, get this site completed and released by the weekend. (Demo version anyway)

Thanks, slightwv
If it was not for you mentioning the string_agg, I would not have found where I had used the STUFF function at.
Thank you and thanks for the string_agg, is going to be a very useful tool.