Nested SELECT statement in a CASE expression

Question

Greetings,

Here is my problem.
I need to get data from multiple rows and return them as a single result in a larger query.

I already posted a similar question here. Return multiple values in one column within a main query but I suspect my lack of SQL knowledge made the question too vague because the answers did not work.
I am using Microsoft SQL 2005.
Here is what I have.

Multiple tables with CaseID as the PK, CaseID is unique.
One table (tblKIN) with CaseID and ItemNum(AutoInc) as the combined PK.
Because each person in the database will likely have more than one relative.
If I run the following, in a SQL query window, it works.

DECLARE @KINList varchar(1000)  
SELECT @KINList = coalesce(@KINList + ', ','') + KINRel from tblKIN  
WHERE CaseID = 'xxx' and Address = 'yyy'  
ORDER BY KINRel  
SELECT @KINList

This will return the relation of all people who live at the same address. the results look like this...
Father, Niece, Sister, Son

Now, the problem for me is how do I add that to my main query?
Shortened to relevant information, the main query looks like this.

SELECT DISTINCT  
c.CaseID,  
c.Name,  
c.Address,  
Relatives=CASE WHEN exists(select k.CaseID from tblKIN k where c.CaseID = k.CaseID)  
   THEN DECLARE @KINList varchar(1000)  
        SELECT @KINList = coalesce(@KINList + ', ','') + KINRel from tblKIN  
        WHERE CaseID = 'xxx' and Address = 'yyy'  
        ORDER BY KINRel  
        SELECT @KINList  
   ELSE ''  
   END
FROM tblCase c  
ORDER BY c.CaseID

The errors I receive are.
Server: Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'DECLARE'.
Server: Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'ELSE'.

I tried nesting inside parenthesis from the DECLARE to the end of the SELECT @KINList.
I tried adding a BEGIN and END to the THEN section of the CASE statement.
Neither worked.

The source table data looks something like this. (periods added for readability)
tblCase
CaseID Name Address
10-001 Jim......100 Main St.
10-002 Tom....150 Elm St.
10-003 Abe.....200 1st St.

tblKIN
CaseID ItemNum Name Relation Address
10-001 00001 Steve...Son........100 Main St.
10-002 00002 James..Father....150 Elm St.
10-002 00003 Betty....Niece......150 Elm St.
10-002 00004 Greta...Sister.....150 Elm St.
10-002 00005 Davey..Son........150 Elm St.
10-003 00006 Edgar...Brother...200 1st St.

If I run the query for CaseID = 10-002, it needs to return the following.
CaseID Name Address.......Relatives
10-002 Tom...150 Elm St. ..Father, Niece, Sister, Son

I am sure this is probably a simple fix, but I just don't know how to do it.
Thank you for your time, and I apologize for the length of the question, but I wanted to be clear.

Thanks !!!

score 0 · Answer 1 · answered Dec 13 '10 at 21:18

You can create a FUNCTION which takes in the caseID as the arguement and returns true or false.

Since you are calling the nested query multiple times, its definitely a performance hit. A better solution is to execute the query and store the results in a temporary table. Then pass this temporary table and the caseID to the FUNCTION and check for containment.

score 0 · Accepted Answer · answered Dec 13 '10 at 21:22

When I did something similar I had to create a scalar function to do the coalesce that returns the varchar result. Then just call it in the select.

CREATE FUNCTION GetRelatives 
(
    @CaseID varchar(10)
)
RETURNS varchar(1000)
AS
BEGIN

    DECLARE @KINList varchar(1000)  
    SELECT @KINList = coalesce(@KINList + ', ','') + KINRel from tblKIN  
        WHERE CaseID = @CaseID  
    ORDER BY KINRel  

    RETURN @KINList 

END

Then your select

SELECT DISTINCT  
c.CaseID,  
c.Name,  
c.Address,  
database.dbo.GetRelatives(c.CaseID) AS Relatives
FROM tblCase c  
ORDER BY c.CaseID

Nested SELECT statement in a CASE expression

2 Answers2