Better way to do this? - SQL Server Forums

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

SQL Server Development (2000)

Better way to do this?

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-17 : 17:53:11

I've got three tables (sample code below) called Reports, Users, and UserReportPermission. Users are allowed all of the reports, except the ones that are in UserReportPermission. I'm using a "WHERE NOT EXISTS" with a subquery. Is this the best way to do this, or is there a LEFT JOIN type solution that I'm overlooking?

In production there will be much more data than this but here's an example of the problem.

 
CREATE TABLE #Users(UserID INT, UserName VARCHAR(50))
	INSERT INTO #Users(UserID, UserName) VALUES(1234, 'User 1234')
	INSERT INTO #Users(UserID, UserName) VALUES(5678, 'User 5678')

CREATE TABLE #Report(ReportID UNIQUEIDENTIFIER, ReportName VARCHAR(50))
	INSERT INTO #Report(ReportID, ReportName) VALUES('94A483B9-0A77-47C3-87DB-3699A0740E9B', 'Report 1')
	INSERT INTO #Report(ReportID, ReportName) VALUES('8DF0BFF9-7C2B-4CC3-9FAE-3531E179DA73', 'Report 2')
	INSERT INTO #Report(ReportID, ReportName) VALUES('9D2A901E-35ED-40D3-90D3-34AA812718B9', 'Report 3')

--This table contains a record
--for each report that a user
--is denyed.
--By Default, user get all reports
--in this table, excpect the ones they 
--are specificlly denyed
CREATE TABLE #UserReportPermission(UserID INT, ReportID UNIQUEIDENTIFIER)
	INSERT INTO #UserReportPermission(UserID, ReportID) VALUES('1234', '94A483B9-0A77-47C3-87DB-3699A0740E9B')
	INSERT INTO #UserReportPermission(UserID, ReportID) VALUES('5678', '8DF0BFF9-7C2B-4CC3-9FAE-3531E179DA73')

DECLARE @UserID INT
--SET @UserID = '1234'
SET @UserID = '5678'

SELECT r.ReportName
FROM #Report r
WHERE NOT EXISTS(SELECT 1 FROM #UserReportPermission urp WHERE r.ReportID = urp.ReportID AND urp.UserID = @UserID)

DROP TABLE #UserReportPermission
DROP TABLE #Report
DROP TABLE #Users

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Lavos
Posting Yak Master

200 Posts

Posted - 2002-10-17 : 18:54:26

Yeah, there's a left join that you're missing. Whether it's better or not, you'll have to view the execution plans and the like.


SELECT r.ReportName
FROM #Report r LEFT JOIN
   (SELECT ReportID FROM #UserReportPermission WHERE UserID = @UserID) rp  -- derived table
    ON (r.ReportID = rp.ReportID)
WHERE rp.ReportID IS NULL

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Edited by - Lavos on 10/17/2002 18:54:59

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-17 : 19:33:11

On my production tables / queries:

With the "NOT EXISTS"
Subtree Cost 0.0817

With the "LEFT JOIN"
Subtree Cost 0.119

Looks like I had the fastest solution already. When I get some more test data, I'll re-bench the two different ways and see what I come up with.

Thanks for your help!!
Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Edited by - michaelp on 10/17/2002 19:35:44

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources