SQLTeam.com | Weblogs | Forums

Subquery returned more than 1 value ... blah blah blah

Hello.

The following query returns no duplicates.

Full_Name = (Select Distinct i.D1Name From Individual i Join AccountIndividual ai On ai.IndividualKey=i.IndividualKey Join Loan ln on ln.MemberNumber=ai.MemberNumber AND ln.LoanNumber=ai.AccountNumber Where i.IsAccountOwner=-1)

However, when I introduce it as a subquery, I get the annoying:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

What would be causing the error since I have no duplicates when I use it as a standalone? Thanks.

is this in where clause?

Full_Name = (

If it's only 1 row, add a clause that "tells" SQL that it can only ever be 1 row, and I think SQL will be OK with it:

Full_Name = (Select Distinct Top (1) i.D1Name From Individual i Join AccountIndividual ai On ai.IndividualKey=i.IndividualKey Join Loan ln on ln.MemberNumber=ai.MemberNumber AND ln.LoanNumber=ai.AccountNumber Where i.IsAccountOwner=-1)

Having no duplicates does not indicate that there are not multiple rows - and a subquery cannot return multiple rows.

You can use TOP 1 to get a single row - or you need to use a correlated subquery to identify the single row associated with the outer query.

Thanks for the responses.

Top (1) brings back the first D1Name for all of the account numbers.

What you need then is a correlated subquery - or a better option might be using a cross apply.

CROSS APPLY (
Select Top (1) i.D1Name 
From Individual i 
Join AccountIndividual ai On ai.IndividualKey=i.IndividualKey 
Join Loan ln on ln.MemberNumber=ai.MemberNumber 
            and ln.LoanNumber=ai.AccountNumber Where i.IsAccountOwner=-1
WHERE {key column from this query} = {key column from outer query}
ORDER BY {some column that identifies most current record}

The related column could be IndividualKey - or it could be MemberNumber - the ordering column would be some type of date (in descending order to get the latest row for the member).

Sorry - but cannot get any more specific without seeing the rest of the query, and possibly would need sample data to test.

Thanks for the response. It worked once I added the Order By.