How to fix "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS" error?

Question

I'm trying to run the following query on MS SQL 2012 Express:

Select (
    Select Id, Salt, Password, BannedEndDate
    from Users
    where username = '" + LoginModel.Username + "'
), (
    Select Count(*)
    From LoginFails
    where username = '" + LoginModel.Username + "'
    And IP = '" + Request.ServerVariables["REMOTE_ADDR"] + "')"
);

But I get the following error:

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

How can I solve this problem?

You are selecting multiple columns in your first sub-query. But each sub query can only return one column. — Tim Schmelter, Jul 01 '13 at 11:36
You should also read up on SQL injection. You are using a request variable directly in a dynamically created SQL - Very bad coding style. — , Jul 01 '13 at 11:40
i am using parameters for every query that has user input, the username can only contain numbers and letters. This is being checked by the model. (I'm using asp.net mvc). Thanks for the tip though! — Jamie, Jul 01 '13 at 11:47

Devart · Answer 1 · 2013-07-01T11:49:17.220

7

Try this one -

"SELECT 
       ID, Salt, password, BannedEndDate
     , (
          SELECT COUNT(1)
          FROM dbo.LoginFails l
          WHERE l.UserName = u.UserName
               AND IP = '" + Request.ServerVariables["REMOTE_ADDR"] + "'
      ) AS cnt
FROM dbo.Users u
WHERE u.UserName = '" + LoginModel.Username + "'"

edited Jul 01 '13 at 11:49

answered Jul 01 '13 at 11:38

Devart

119,203
23
166
186

Thanks for your comment, but i chose to use the query of mortb because it's a bit shorter – Jamie Jul 01 '13 at 11:45

score 7 · Accepted Answer · edited Dec 21 '15 at 03:25

Try this:

 Select 
    Id, 
    Salt, 
    Password, 
    BannedEndDate, 
    (Select Count(*) 
        From LoginFails 
        Where username = '" + LoginModel.Username + "' And IP = '" + Request.ServerVariables["REMOTE_ADDR"] + "')
 From Users 
 Where username = '" + LoginModel.Username + "'

And I recommend you strongly to use parameters in your query to avoid security risks with sql injection attacks!

Hope that helps!

How to fix "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS" error?

2 Answers2

Linked