How to Optimize this SQL Query?

Question

I have 3 tables:

• CRSTasks (ID,parentID)
• CRSTaskReceivers (ID,tskID,receiverID)
• UserNames (id,name)

...relation between CRSTasks and CRSTaskReceivers one-to-many
between UserNames and CRSTaskReceivers one-to-one

tasks   
ID   parent  
1     null    
10     1      
50     1

taskReceivers  
id      taskID    receiverID  
1        1          4(john)  
1        10         2(mike)  
1        50         3(brand)  

I need result like that:

taskid    Receivers
------------------- 
1           jone,mike,brand   

ONLY FOR PARENT TASKS IT WILL CONCATE RECEIVERS

Answer 1

Besides the odd string concatenation going on it sure looks like all that could be done in one query instead of four. It's perfectly fine to have more than one criteria in a join. Something along:

FROM   CRSTaskReceiver
   INNER JOIN CRSTask
        ON  CRSTaskReceiver.CRSTaskID = CRSTask.ID
   INNER JOIN CRS_BuiltinGroup
        ON  CRSTaskReceiver.ReceiverID = CRS_BuiltinGroup.ID AND CRSTaskReceiver.ReceiverType = 4
WHERE  CRSTask.ParentTask = @TaskID

Also the below part of the function seems to do absolutely nothing. What is it meant to do?

DECLARE @tmpLength INT
SET @tmpLength = 0
SET @tmpLength = LEN(@tmp)
IF @tmpLength > 0
BEGIN
    SET @tmp = SUBSTRING(@tmp, 0, @tmpLength)
END 

Answer 2

SQL Server 2005+:

---

SELECT t.id AS taskid,
       STUFF((SELECT ','+ x.name
                FROM (SELECT COALESCE(pu.[ArabicName], aut.Name) AS name
                        FROM CRSTaskReceivers tr 
                        JOIN AD_USER_TBL aut ON aut.id = tr.receiverid
                   LEFT JOIN PORTAL_USERS pu ON pu.id = aut.id
                       WHERE tr.crstaskid = t.id
                         AND tr.receivertype = 1
                      UNION
                      SELECT agt.name
                        FROM CRSTaskReceiver tr
                        JOIN AD_GROUP_TBL sgt ON agt.id = tr.receiverid
                       WHERE tr.receivertype = 3
                         AND tr.crstaskid = t.id) x
         FOR XML PATH('')), 1, 1, '')
  FROM CRSTasks t

Don't need the function.