Need to not show some properties in select stateme

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need to not show some properties in select stateme

Author

Topic

mutlyp
Starting Member

20 Posts

Posted - 2012-05-07 : 13:06:50

I have this query:

Select *
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

When I run this it returns iTaskID, iFaciityID, sCat_Name, sSubCat_Name, sSpec_Name, iTaskID, iFacilityID and Occ.
Is there anyway to write this query so it WONT return the second iTaskID and the second iFacilityID and Occ?
So all the query would return is:
iTaskID, iFaciityID, sCat_Name, sSubCat_Name, sSpec_Name

Thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-05-07 : 13:56:43

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources