Count in 'in' SQL Server

Question

I have a problem with:

select  UsersInfo.fName 'نام', UsersInfo.lName 'نام خانوادگی' 
from UsersInfo 
where UsersInfo.MemberID in (
    select COUNT(ToLend.UserID), ToLend.UserID  'تعداد کتاب های قرض گرفته'  
    from ToLend
    inner join UsersInfo on ToLend.UserID = UsersInfo.MemberID
    group by UserID
    having count(UserID) > 2
)

Error is:

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

In this code, how can "count" for UserID?

Answer 1

If you want to know which user is lended > 2, you can remove COUNT(ToLend.UserID) in subquery. And if you want to know how many times is lended you can add subquery in select

select  UsersInfo.fName 'نام', 
        UsersInfo.lName 'نام خانوادگی',
        (select COUNT(UserID)
         from ToLend
         where UserID = UsersInfo.MemberID
         group by UserID
         having count(UserID) > 2) 'Lended count'
from UsersInfo 
where UsersInfo.MemberID in (
    select ToLend.UserID  'تعداد کتاب های قرض گرفته'  
    from ToLend
    inner join UsersInfo on ToLend.UserID = UsersInfo.MemberID
    group by UserID
    having count(UserID) > 2
)