sub query - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

sub query

Author

Topic

kt
Yak Posting Veteran

88 Posts

Posted - 2011-10-03 : 12:32:35

Hi,

I have City and State are 1-m. One State has more than one CityCode. C can't do join here, since it will returnned duplicated records, so I want to do the sub query but I got an error below.

select a.*, (select b.CityCode from City b where a.ID = b.ID) as Name
from State a
where a.ID in( '586')

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

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 13:00:32

what should be your output? one city for each state?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-10-03 : 13:02:19

Use outer apply

kt
Yak Posting Veteran

88 Posts

Posted - 2011-10-03 : 13:02:23

is that possible to return as the list?
such as
Texas: AU,HOU, DAL....?

quote:
Originally posted by visakh16

what should be your output? one city for each state?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 13:05:06

[code]select a.*,
stuff((select ','+b.CityCode from City where ID = a.ID for xml path('')),1,1,'') as cityName
from State a
where a.ID in( '586')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kt
Yak Posting Veteran

88 Posts

Posted - 2011-10-03 : 13:07:41

can you give me some example please?

quote:
Originally posted by namman

Use outer apply

kt
Yak Posting Veteran

88 Posts

Posted - 2011-10-03 : 13:17:33

it returned the list,however, it returned all values in the child table (city) not only the value of id = 586.

any idea?

quote:
Originally posted by visakh16

select a.*,
stuff((select ','+b.CityCode from City where ID = a.ID for xml path('')),1,1,'') as cityName
from State a
where a.ID in( '586')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kt
Yak Posting Veteran

88 Posts

Posted - 2011-10-03 : 13:20:02

never mind, it worked , thank you soooooooooooo much

quote:
Originally posted by visakh16

select a.*,
stuff((select ','+b.CityCode from City where ID = a.ID for xml path('')),1,1,'') as cityName
from State a
where a.ID in( '586')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 13:32:05

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources