How to handling if subquery returns multiple value

Question

I have a stored procedure in which I'm using sub query, but the issue is that my sub query returns 2 values and I've to return all records based on these two values.

Select * from [Address] where AddressID=
(Select AddressID from PersonAddress where PersonID=
(select Claimant from [Case] where CaseID=35))

In this query AddressID returning two values and both the value having record in table, I've to return both the address.

How can I solve this?

Answer 1

Instead of = use IN:

Select * from [Address] where AddressID IN
(Select AddressID from PersonAddress where PersonID IN
(select Claimant from [Case] where CaseID=35))

or try JOIN, the correct way:

Select * from Address a
inner join PersonAddress p on a.AdressID = p.AddressID
inner join Case c on p.PersonID = c.Claimant
where c.CaseID = 35

Answer 2

You have two options:

• use IN clause like this:

  Select * from [Address] where AddressID IN ( Select AddressID from PersonAddress where PersonID IN (select Claimant from [Case] where CaseID=35) )

• or limit your subqueries with TOP clause

  Select * from [Address] where AddressID= (Select TOP 1 AddressID from PersonAddress where PersonID= (select TOP 1 Claimant from [Case] where CaseID=35))