tricky restriction logic - 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

Transact-SQL (2000)

tricky restriction logic

Author

Topic

djseng
Starting Member

25 Posts

Posted - 2004-03-24 : 17:26:40

Ok, this one is driving me crazy... Hopefully, I can describe this adequately.

I have a products table and those products are restricted by the state the user is coming from. This was no big deal until it was decided to allow regional users to use the site. Anyway, I'm trying to figure out how to fix my sql so a regional user can see all ads that they are allowed to see, and not the ones they aren't allowed to see.

The way the data is setup as follows (describing only needed information for this problem)

Products (ProductId, AllowOrDeny)
ProductRestrictions (ProductId, State)
Regions (Region, State)
User (State, Code)

I think the main thing that is throwing me off is the AllowOrDeny field in the Products table. The AllowOrDeny field is a bit field and if it is 0 then all states in the ProductRestrictions table are denied access to that product, if 1 then all states in the ProductRestrictions table are the only states that can see that product.

In the users table the state field is a two character code and the code field is a two character code. In the design if the first character of the code field is an alpha character then that user is a regional user and the state field is actually interpreted as a region code.

Here is a really stripped down version of the procedure:


IF (ISNUMERIC(SUBSTRING(3,1,@RepCode) = 1)
   -- State User
   BEGIN
	SELECT P.*
	FROM Products P WITH (NOLOCK)
		LEFT OUTER JOIN ProductRestrictions PR WITH (NOLOCK) 
			ON (PR.ProductID = P.ProductId AND PR.State = LEFT(@RepCode,2))
	WHERE ((P.AllowOrDeny = 0 AND PR.StateCode IS NULL)
			OR (P.AllowOrDeny = 1 AND PR.StateCode IS NOT NULL))
   END
ELSE
    --- Regional User
    BEGIN
	SELECT P.*
	FROM Products P WITH (NOLOCK)
		LEFT OUTER JOIN (
			SELECT DISTINCT PR.ProductID, PR.State
			FROM ProductRestrictions PR
				INNER JOIN Regions R
					ON PR.State = Regions.State
			WHERE R.Region = LEFT(@RepCode, 2)
		) As StatesRes ON P.ProductId = StatesRes.ProductId
	WHERE ((AllowOrDeny = 0 AND StatesRes.State IS NULL)
		OR (AllowOrDeny = 1 AND StatesRes.State IS NOT NULL))
   END

The second part of this conditional statement is wrong. The second statement will not return products that are restricted in one state in the region but are allowed in other states of the region.

I thought I would get this out to more people so that I can have more than one mind working on the problem. I will keep brainstorming myself, check back periodically, and post and answer if I come up with it myself.

Thanks in advance to anyone who can figure this stumper out.

I hope I explained this problem thoroughly enough but any questions I will try to answer. No I can't change the data structure.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-24 : 20:03:05

Can you post some DDL and sample data to demonstrate your problem?

djseng
Starting Member

25 Posts

Posted - 2004-03-25 : 12:05:23

Here's some DDL and sample data with expected results....

create table Products (ProductId int, AllowOrDeny bit)
create table ProductRestrictions (ProductId int, State char(2))
create table Regions (Region char(2), State char(2))
create table Users (State char(2), Code char(4))

insert Products values (1, 0)
insert Products values (2, 1)
insert Products values (3, 0)
insert Products values (4, 1)

insert ProductRestrictions values (1, '01')
insert ProductRestrictions values (1, '02')
insert ProductRestrictions values (2, '02')
insert ProductRestrictions values (2, '03')
insert ProductRestrictions values (3, '03')
insert ProductRestrictions values (3, '04')
insert ProductRestrictions values (4, '01')
insert ProductRestrictions values (4, '02')
insert ProductRestrictions values (4, '03')

insert Regions values ('01', '01')
insert Regions values ('01', '02')
insert Regions values ('01', '03')
insert Regions values ('01', '04')
insert Regions values ('02', '04')
insert Regions values ('02', '05')
insert Regions values ('03', '04')
insert Regions values ('04', '05')
insert Regions values ('04', '06')

insert Users values ('01', '1234')
insert Users values ('02', '1234')
insert Users values ('01', 'A123')
insert Users values ('02', 'C123')
insert Users values ('03', 'B123')
insert Users values ('04', 'D123')

ProductIds for user ('01', '1234')
3
4

ProductIds for user ('02', '1234')
2
3

ProductIds for user ('01', 'A123')
1
2
3
4

ProductIds for user ('02', 'C123')
1
3

ProductIds for user ('03', 'B123')
1
3

ProductIds for user ('04', 'D123')
1
3

djseng
Starting Member

25 Posts

Posted - 2004-03-25 : 14:27:46

Ok, I think I have the answer. This query is for the regional user piece, the piece that I was trying to figure out...
If anyone sees anything wrong with this please let me know... A colleague came up with this one. This one looks pretty scary, so understandable if no one cares to look at it

Thanks,
Dave


DECLARE @RepCode char (6)
SET @RepCode = '03A234'
SELECT P.ProductId, P.AllowOrDeny
FROM Products P WITH (NOLOCK)
	LEFT OUTER JOIN (
		SELECT PR.ProductID, PR.State
			FROM ProductRestrictions PR 
			INNER JOIN Regions R 
				ON (PR.State = R.State)
			LEFT OUTER JOIN Products 
				ON PR.ProductId = Products.ProductId
			WHERE R.Region = LEFT(@RepCode, 2)
			AND   ((AllowOrDeny = 0 AND PR.State IS NULL)
				OR (AllowOrDeny = 1 AND PR.State IS NOT NULL))
			GROUP BY PR.ProductId, PR.State
		) AS StatesRes 
		ON (P.ProductId = StatesRes.ProductID)
WHERE ((AllowOrDeny = 0 AND StatesRes.State IS NULL)
	OR (AllowOrDeny = 1 AND StatesRes.State IS NOT NULL))
GROUP BY P.ProductId, P.AllowOrDeny

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources