Through Multiple-Column Subquery, to get duplicate records [Resolved]

Posted by Sriharim under Sql Server on 5/8/2015 | Points: 10 | Views : 343 | Status : [Member] | Replies : 1
I want to get student name details whose having student id and class id duplicate records through Multiple-Column Subquery.

Create Table TestNameList ( StudentId int, StudName varchar(50), ClassId int )

Insert into TestNameList values(1,'John',1)
Insert into TestNameList values(2,'John',2)
Insert into TestNameList values(1,'John',1)
Insert into TestNameList values(2,'John',3)
Insert into TestNameList values(3,'John',3)
Insert into TestNameList values(3,'Joshn',3)
select StudentId,StudName,ClassId from TestNameList 
where (StudentId,ClassId) in (select studentid,ClassId from TestNameList group by studentid,ClassId having COUNT(*)>1)


For above code, error message is :
An expression of non-boolean type specified in a context where a condition is expected, near ','.

I think T-sql supports Multiple-Column Subquery,
does T-sql supports Multiple-Column Subquery ? then why this error is there ?
How can i get student name details Thorugh Multiple-Column Subquery ?
Please help me

---
Srihari



Responses

Posted by: Bandi on: 5/8/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Your query fails with error message 'Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near ','.
'


Condition in the WHERE clause expects only boolean result.....

The alternate solution for your requirement is as follows,
select distinct t1.StudentId,StudName,t1.ClassId 
from TestNameList t1
JOIN (select studentid, ClassId from TestNameList group by studentid,ClassId having COUNT(*)>1) t2
ON t1.StudentId = t2.StudentId and t1.ClassId=t2.ClassId


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response