Subquery returned more than 1 value? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 returned more than 1 value?

Author

Topic

markshen2006
Starting Member

15 Posts

Posted - 2008-04-15 : 16:52:47

Hi

I have two tables and I need write a query that include subquery that return more than 1 value,Can you give me a idea how to write the query

The table with data is like this,one file maybe belong to many group.

Filename FilegroupID

file1 1
file2 2
file1 3
file1 4
file3 1
file2 5

I need write a query to get the result like this

file1 1,3,4
file2 2,5
file3 1

I try to write a query with subquery but I get error message

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"

I use SQL Server 2005.but I need the subquery return data like "1,3,4".

Please help me.

Thanks

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 16:57:07

see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"

markshen2006
Starting Member

15 Posts

Posted - 2008-04-15 : 20:47:32

Hi Peso,

I have taken look the link you posted, but I do not think I can get answer from that post.

Please help me or give me other solution.

Thanks

Mark

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 23:36:35

Why do you think link Peso gave you dont work for you?

ranganath
Posting Yak Master

209 Posts

Posted - 2008-04-16 : 00:43:34

Hi,

use this

declare @StrConcat table (col1 nvarchar(10),col2 nvarchar(10))

insert into @StrConcat
select 'file1','1' union all
select 'file1','2'union all
select 'file1','3'union all
select 'file2','1' union all
select 'file2','2'union all
select 'file3','2' union all
select 'file3','3'

select col1, stuff( ( select ','+ col2 from @StrConcat t1 where t2.col1 = t1.col1 for xml path('')),1,1,'') GroupId
from @StrConcat t2
group by col1
order by col1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-16 : 02:59:43

quote:
Originally posted by markshen2006

Hi Peso,

I have taken look the link you posted, but I do not think I can get answer from that post.

Please help me or give me other solution.

Thanks

Mark

If you use SQL Server 2005, then it would work
Otherwise you need to have a user defined function

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-16 : 03:06:04

quote:
Originally posted by markshen2006

I have taken look the link you posted, but I do not think I can get answer from that post.

You "think" or you have test so that you know for fact?

E 12°55'05.25"
N 56°04'39.16"

markshen2006
Starting Member

15 Posts

Posted - 2008-04-16 : 09:51:07

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources