More than one value returned by subquery

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

More than one value returned by subquery

Author

Topic

6233114
Starting Member

4 Posts

Posted - 2009-06-27 : 04:09:15

I am getting 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 know that the subquery is returning more than one value, but I don't know how to write the sql code to get around this (normally use IN, but how do I incorporate that into the subquery?).

I am attempting to extract the international name of a language (not the english name) determined by the language that the user is currently viewing (according to the LanguagesAvailable.LanguageCode)

Here is my sql code:

@UserID uniqueidentifier
--3ac6a79a-ba08-4ae1-b2bf-fda3529c7ee4

AS

SELECT LanguageVersion.LanguageVersionID,
LanguageVersion.LanguageAvailableID,
CountryFlag.CountryFlagIconPath,
(
SELECT LanguagesAvailable.LanguageAvailableDescriptionIntl
FROM LanguagesAvailable
WHERE LanguagesAvailable.LanguageAvailableDescriptionEng IN
(
--this inner query will return the english name of the language that the user has saved to the language version table (English - Australia).
SELECT LanguagesAvailable.LanguageAvailableDescriptionEng
FROM LanguagesAvailable, LanguageVersion
WHERE LanguagesAvailable.LanguageAvailableID = LanguageVersion.LanguageAvailableID
AND LanguageVersion.UserID = @UserID
)
AND LanguagesAvailable.LanguageCode =
(
--this inner query will return the current language code (en-AU) of the user.
SELECT LanguagesAvailable.AltNativeLanguageCode
FROM LanguagesAvailable, LanguageVersion, LanguageView
WHERE LanguagesAvailable.LanguageAvailableID = LanguageVersion.LanguageAvailableID
AND LanguageVersion.LanguageVersionID = LanguageView.LanguageVersionID
AND LanguageView.UserID = @UserID
)
)

FROM LanguageVersion, LanguagesAvailable, Country, CountryFlag

WHERE LanguageVersion.UserID = @UserID
AND LanguagesAvailable.LanguageAvailableID = LanguageVersion.LanguageAvailableID
AND LanguagesAvailable.CountryID = Country.CountryID
AND Country.CountryFlagID = CountryFlag.CountryFlagID

Any help or guidance would be appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-27 : 04:16:52

,(SELECT TOP 1 ...

E 12°55'05.63"
N 56°04'39.26"

6233114
Starting Member

4 Posts

Posted - 2009-06-27 : 04:51:03

quote:
Originally posted by Peso

,(SELECT [redTOP 1[/red] ...

wha....what????

can you post again?

6233114
Starting Member

4 Posts

Posted - 2009-06-27 : 05:26:51

quote:
Originally posted by Peso

,(SELECT TOP 1 ...

Thanks for the reply Peso, although I thought that the use of select top 1 worked, it simply returns the first row of the sub query.

The purpose of the subquery is to output/replace the LanguagesAvailable.LanguageAvailableDescriptionIntl (according to the language code), which should replace the relative LanguagesAvailable.LanguageAvailableDescriptionIntl.

Any further suggestions?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-27 : 06:49:19

We don't know YOUR business rules of which record to get...

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources