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

Posted by Sriharim under Sql Server on 5/8/2015 | Points: 10 | Views : 1270 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Mark as Answer if its helpful to you
---
Srihari

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts