CODEWITHSUNDEEP
sqlsql-serverdatabase
Baqer Naqvi
Baqer Naqvi

Reputation: 6504

Error : SubQuery returns more than one records

I am trying to execute a query to fetch all students that belongs to a specific hostel,with hostel id, on SqlServer but facing the error Subquery returned more than 1 value. query is :

select * from students 
where StudentID=(select studentID from Student_To_Hostel where hostelID=2)

How to fix it ???

Upvotes: 2

Views: 62

Answers (3)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Method 1:

You need to use JOIN here

Try like this

SELECT S.* 
From Students S Inner JOIN Student_To_Hostel SH ON
              SH.StudentID =S.StudentID
WHERE SH.hostelID=2

Method2:

You can use IN Clause

SELECT * 
FROM students 
where StudentID IN (
                    SELECT studentID FROM Student_To_Hostel where hostelID=2
                   )

Upvotes: 6

mohan111
mohan111

Reputation: 8865

this is another way to do 

select * from students 
where EXISTS (select studentID from Student_To_Hostel where hostelID=2)

Upvotes: 0

vishad
vishad

Reputation: 1164

Try replacing the '=' sign in the outer query with 'in'. 

   select * from students 
   where StudentID in (select studentID from Student_To_Hostel where hostelID=2)

Hope it Helps

Vishad

Upvotes: 1

Related Questions