Problem with subquery - 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)

Problem with subquery

Author

Topic

texas1992
Starting Member

21 Posts

Posted - 2012-09-14 : 09:47:39

I have this query:


	SELECT DISTINCT LayoutName,
	(SELECT Published From FloorplanObjects b Where FloorID = 29 and Active = 'Y' AND b.LayoutName = a.LayoutName GROUP BY Published) AS Published
	FROM dbo.FloorplanObjects a
	WHERE FloorID = 29 and Active = 'Y' AND a.CreatedBy = 'kanen'
	ORDER BY LayoutName

I am trying to debug this because I am getting errors. The one error that I need to work on first is when I run the subquery I get the error "The multi-part identifier "a.LayoutName" could not be bound."

When I run the whole query I get the error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

I don't understand because everything worked last night when I left and now it doesn't.

Please help!

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 10:25:12

you cant return multiple values through subquery. it has to be single value. dont understand what exactly you're trying to achieve here looks like below


        SELECT DISTINCT LayoutName,
	Published
	FROM dbo.FloorplanObjects a
	WHERE FloorID = 29 and Active = 'Y' AND a.CreatedBy = 'kanen'
	ORDER BY LayoutName

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

texas1992
Starting Member

21 Posts

Posted - 2012-09-14 : 10:37:59

Thanks for your help.

I understand that I can't return multiple values from a subquery. This won't because of the b.LayoutName = a.LayoutName in the where statement.

Also, why do I get the error "The multi-part identifier "a.LayoutName" could not be bound."?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 10:42:03

you will not get that error if you run full query

running subquery alone will not have table dbo.FloorplanObjects a in scope so it doesnt know what a means hence the error

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

texas1992
Starting Member

21 Posts

Posted - 2012-09-14 : 11:21:11

I found the problem was with the data. Sorry for the inconvenience.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources