mssql exception subquery returned more than 1 value Edit

Murugan Andezuthu Dharmaratnam | 15 September 2020 | 6

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How to reproduce the error

I have two tables articles and articleedit. I have a column named Edited which is available only in articleedit table. when i execute the command select * from Article it returns all the rows in the article table. I want to select Article.* plus Edited.

if you execute the query below it will throw the error Subquery returned more than 1 value

select Article.*,(select Edited from ArticleEdit Where ArticleId=Article.Id) as Edited from Article

Solution

the solution is to modify the subquery to return only one row, either by using where condition or using select top(1) in the subquery. check the working query below.

select Article.*,(select top(1) Edited from ArticleEdit Where ArticleId=Article.Id) as Edited from Article