Change select * to only the columns you want.
By the way, I had to format your query in order to figure out where those others were coming from. Please format your queries in the future so that we can see exactly what it's doing. Yours seems to coded by Enterprise Manager or something. Putting the JOINs at the end of lines is just bad formatting.
Select iTaskID, iFaciityID, sCat_Name, sSubCat_Name, sSpec_Name
FROM
(
SELECT
dbo.jha_Map_Task_Facilities.iTaskID, dbo.jha_Map_Task_Facilities.iFacilityID,
dbo.jha_Categories.sCat_Name, dbo.jha_SubCategories.sSubCat_Name,
dbo.jha_Specifics.sSpec_Name
FROM dbo.jha_Categories
INNER JOIN dbo.jha_TaskList ON dbo.jha_Categories.iCatID = dbo.jha_TaskList.iCatID
INNER JOIN dbo.jha_Map_Task_Facilities ON dbo.jha_TaskList.iTaskID = dbo.jha_Map_Task_Facilities.iTaskID
LEFT OUTER JOIN dbo.jha_SubCategories ON dbo.jha_TaskList.iSubCatID = dbo.jha_SubCategories.iSubCatID
LEFT OUTER JOIN dbo.jha_Specifics ON dbo.jha_TaskList.iSpecID = dbo.jha_Specifics.iSpecID
WHERE dbo.jha_TaskList.iIsActive = 1
)t
INNER JOIN
(
SELECT
dbo.jha_Map_Task_Facilities.iTaskID, dbo.jha_Map_Task_Facilities.iFacilityID,
SUM(CASE WHEN dbo.jha_Map_Task_Facilities.iFacilityID = 21 THEN 1 ELSE 0 END) OVER (PARTITION BY dbo.jha_Map_Task_Facilities.iTaskID) AS Occ
FROM dbo.jha_Map_Task_Facilities
)t1
ON t1.iTaskID = t.iTaskID AND t1.iFacilityID = t.iFacilityID AND t1.Occ=0
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Subscribe to my blog
Need to not show some properties in select stateme
Posted - 05/07/2012 : 13:06:50
