views:

89

answers:

0

I have this query, our friend "OMG Ponies" help me to make it :) :-

SELECT t.id AS taskid,
       STUFF(
           (
               SELECT ',' + x.tID
               FROM   (
                          SELECT CAST(id AS VARCHAR(200)) AS tid
                          FROM CRSTask c WHERE c.ParentTask = 
                          7562 -- comment this line
                      ) x
                      FOR XML PATH('')
           ),
           1,
           1,
           ''
       ) AS strIDS
FROM   CRSTask t
WHERE t.ParentTask IS NULL
AND t.id = 7562 -- comment this line  

the result of this query will be:

 id          strIDS  
7562    7615,7616,7617,7618,7619,7620,7621,7631,7632,123

this is good.
but when i tried to replace the strIDS with its names from another table it take alot of time.

e.g. 123 [the last id in strIDS] is a taskID from CRSTask, has a relation ont-to-one to CRSTaskReceiver and taskReceiver has a relation one-to-one to Portal_Users_View(id,userName) -I need to replace 123 with its equivalent userName -I made it using Joins .. it take a lot of time, also using Where between tables take alot of time