Error - Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Question

User-625131191 posted

Am trying to include UserStatus in the procedure below its showing me error, please how do i correct this

Select TOP(12) UserId,UserName,Name,RegisterdDate,About,ImageName FROM [UseraCCOUNT] 
where username IN (SELECT UserStatus,UserName as username FROM VStatus WHERE FKUserName=@UserName AND UserStatus=1)

Answer 1

User475983607 posted

Remove the UserStatus from the subquery.  

where username IN (SELECT UserName as username FROM VStatus WHERE FKUserName=@UserName AND UserStatus=1)

A JOIN is much better solution.

Select TOP(12) 
	ua.UserId,
	ua.UserName,
	ua.Name,
	ua.RegisterdDate,
	ua.About,
	ua.ImageName 
FROM [UseraCCOUNT] as ua
	INNER JOIN VStatus as vs On ua.UserName = vs.FKUserName
WHERE ua.UserName = @UserName AND vs.UserStatus = 1

You can learn TSQL syntax by visiting the SQL docs.

https://docs.microsoft.com/en-us/sql/t-sql/language-reference

SSMS only has a point and click feature called Query Designer for building query graphically.

https://www.mssqltips.com/sqlservertip/1086/sql-server-management-studio-query-designer/

https://docs.microsoft.com/en-us/sql/ssms/visual-db-tools/design-queries-and-views-how-to-topics-visual-database-tools

Answer 2

User-625131191 posted

but you removed this 

UserStatus

my code requires this 

UserStatus

Answer 3

User475983607 posted
Correct, because your syntax is incorrect. If you need UserStatus in the result set then use the join example and add UserStaus to the SELECT. See the TSQL documentation for proper language constructs.

Answer 4

User1400794712 posted

Hi Skyformat48,

I think mgebhard's idea is a good idea. What do you mean with 'requires UserStatus'? Do you mean that you want to select the UserStatus? 

select top(12) 
ua.UserId,ua.UserName,ua.Name,ua.RegisterdDate,ua.About,ua.ImageName,vs.UserStatus
from UseraCCOUNT as ua
inner join VStatus as vs
on ua.UserName = vs.UserName
where vs.FKUserName=@UserName AND vs.UserStatus=1

Best Regards,

Daisy