Q: Select values from one table, where another = value? I am trying to run a SQL query (SQL Server) that will look up a list of names from a subquery.
I have this but I get a "subquery returned more than 1" error:
SELECT FirstName + ' ' + LastName AS Name
FROM Members
WHERE     
   (MemberGUID = (SELECT ClassDetails.Leader
                  FROM Members AS Members_1 
                  INNER JOIN MemberDetails ON Members_1.MemberGUID = MemberDetails.Member 
                  INNER JOIN ClassDetails ON ClassDetails.Class = Members_1.CurrentClass
                  WHERE      
                      (MemberDetails.JoiningDate >= '02/03/2012') 
                      AND (Members_1.FirstName = '*') 
                      OR (MemberDetails.JoiningDate >= '02/03/2012') 
                      AND (Members_1.LastName = '*') 
                      OR (MemberDetails.JoiningDate >= '02/03/2012') 
                      AND (MemberDetails.Email IS NULL) 
                      AND (MemberDetails.MobilePhone IS NULL) 
                      AND (MemberDetails.WorkPhone IS NULL) 
                      AND (MemberDetails.HomePhone IS NULL) 
                      AND (Members_1.CurrentClass <> 339) 
                      AND (Members_1.CurrentClass <> 696) 
                      AND (Members_1.CurrentClass <> 0)))

I need this to return the values FirstName and LastName for every value returned in the subquery. (The subquery returns a list of GUIDs which should be fed into the WHERE MemberGUID statement)
How can this be done?

A: Use in instead of =.
...
WHERE (MemberGUID in ( ... ))

You can also just make it a join instead of using the subquery:
SELECT FirstName + ' ' + LastName AS Name
FROM Members m1
INNER JOIN Members AS Members_1 ON m1.MemberGUID = ClassDetails.Leader
...


A: Offhand, I would think you could just change MemberGUID = to MemberGUID IN.
As the error message states, your subquery is returning many rows, and you can't compare a single value to a collection of many rows.  But you can look for that value in those rows, which is exactly what the IN operator does.
MSDN Reference

A: Nice place to remember existence of ANY, SOME and ALL:
WHERE MemberGUID = ANY ( ... )


A: Add DISTINCT in subquery.
SELECT FirstName + ' ' + LastName AS Name
FROM Members
WHERE MemberGUID IN 
       (SELECT DISTINCT ClassDetails.Leader
        FROM Members AS Members_1 INNER JOIN MemberDetails 
                  ON Members_1.MemberGUID = MemberDetails.Member 
             INNER JOIN ClassDetails 
                 ON ClassDetails.Class = Members_1.CurrentClass
        WHERE    (MemberDetails.JoiningDate >= '02/03/2012') 
                      AND (Members_1.FirstName = '*') 
                      OR (MemberDetails.JoiningDate >= '02/03/2012') 
                      AND (Members_1.LastName = '*') 
                      OR (MemberDetails.JoiningDate >= '02/03/2012') 
                      AND (MemberDetails.Email IS NULL) 
                      AND (MemberDetails.MobilePhone IS NULL) 
                      AND (MemberDetails.WorkPhone IS NULL) 
                      AND (MemberDetails.HomePhone IS NULL) 
                      AND (Members_1.CurrentClass <> 339) 
                      AND (Members_1.CurrentClass <> 696) 
                      AND (Members_1.CurrentClass <> 0)))

