A more optimized way to perform this query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

A more optimized way to perform this query?

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-08-03 : 06:50:33

I am performing a query that shows a list of skills and users, and highlights which skills are not applicable to certain users.
The statement is:


SELECT
	sg.Group,
	s.Skill,
	sgu.userID,
	u.name,
	u.surname,
	ut.notApplicable
FROM
	[tbl_groups-skills] sgs INNER JOIN
	[tbl_groups-Users] sgu ON sgs.groupID = sgu.groupID INNER JOIN
	[tbl_groups] sg on sgs.groupID = sg.groupID INNER JOIN
	[tbl_Skills] s ON sgs.skillID = s.skillID INNER JOIN
	[tbl_Users] u ON u.userID = sgu.userID LEFT OUTER JOIN
	[tbl_UserTargetLevels] ut ON ut.sgsID = sgs.linkID AND ut.userID = u.userID INNER JOIN
	@tbl_users u2 ON u2.userID = sgu.userID INNER JOIN
	@tbl_sgsIds sgs2 ON sgs2.sgsID = sgs.linkID

and produces this result set:


Group			Skill				userID	name	surname	notApplicable
---------------------------------------------------------------------------------------------
Corporate Governance	Business Risk Management	1240	Jon	Bevan	NULL
Corporate Governance	Corporate Governance		1240	Jon	Bevan	1
Corporate Governance	Business Risk Management	1199	Donn	Smith	NULL
Corporate Governance	Corporate Governance		1199	Donn	Smith	1
Corporate Governance	Business Risk Management	1250	Terry	Hobbs	1
Corporate Governance	Corporate Governance		1250	Terry	Hobbs	1
Corporate Governance	Corporate Governance		1250	David	May	NULL
Corporate Governance	Corporate Governance		1250	David	May	NULL

I need to modify the statement so that users that have a value of 1 for all notApplicable skills would be removed from the results. So, in the results above, Terry Hobbs would be removed from the results because that user has notApplicable against both skills shown. The other users would remain as they only have a single notApplicable value, or zero notApplicable values.

I've written a version that works, but I think my new query is extremely long-winded. My approach basically converts the notApplicable bit values to integers, then compares the SUM of them against a second column of integers with the value of 1. I'd appreciate any uber-yaks giving me some pointers on how I might accomplish this in a better way please!


SELECT 
	SUM(c.countInt),
	SUM(c.notApplicable),
	c.userID,
	c.name,
	c.surname
FROM
	(
	SELECT
		sg.Group,
		s.Skill,
		sgu.userID,
		u.name,
		u.surname,
		CAST(ut.notApplicable AS INT) as notApplicable,
		1 as countInt
	FROM
		[tbl_groups-skills] sgs INNER JOIN
		[tbl_groups-Users] sgu ON sgs.skillGroupID = sgu.skillGroupID INNER JOIN
		[tbl_groups] sg on sgs.skillGroupID = sg.skillGroupID INNER JOIN
		[tbl_Skills] s ON sgs.skillID = s.skillID INNER JOIN
		[tbl_Users] u ON u.userID = sgu.userID LEFT OUTER JOIN
		[tbl_UserTargetLevels] ut ON ut.sgsID = sgs.linkID AND ut.userID = u.userID INNER JOIN
		@tbl_users u2 ON u2.userID = sgu.userID INNER JOIN
		@tbl_sgsIds sgs2 ON sgs2.sgsID = sgs.linkID
	) c
GROUP BY
	c.userID,
	c.name,
	c.surname
HAVING
	SUM(c.notApplicable) = SUM(c.countInt)

Sachin.Nand

2937 Posts

Posted - 2011-08-03 : 07:01:27

This ???



..... having SUM(case when notApplicable=1 then 1 else 0 end)!=1

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-03 : 07:28:19

i think this is better

having SUM(case when notApplicable=1 then 0 else 1 end) > 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-08-03 : 08:00:03

Okay thanks for your suggestions. Am I correct in assuming that I have taken the correct approach with using a nested statement, and then using HAVING to select the required rows? I did wonder whether the whole nested select statement was too much work, and whether the solution could be accomplished using just the 'inner' select statement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-03 : 08:04:50

you can achieve it using inner select itself


SELECT
		sgu.userID,
		u.name,
		u.surname,
FROM
		[tbl_groups-skills] sgs INNER JOIN
		[tbl_groups-Users] sgu ON sgs.skillGroupID = sgu.skillGroupID INNER JOIN
		[tbl_groups] sg on sgs.skillGroupID = sg.skillGroupID INNER JOIN
		[tbl_Skills] s ON sgs.skillID = s.skillID INNER JOIN
		[tbl_Users] u ON u.userID = sgu.userID LEFT OUTER JOIN
		[tbl_UserTargetLevels] ut ON ut.sgsID = sgs.linkID AND ut.userID = u.userID INNER JOIN
		@tbl_users u2 ON u2.userID = sgu.userID INNER JOIN
		@tbl_sgsIds sgs2 ON sgs2.sgsID = sgs.linkID
GROUP BY        sgu.userID,
		u.name,
		u.surname
HAVING         SUM(case when notApplicable=1 then 0 else 1 end) > 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources