December 3, 2015 at 9:12 am
Hello All
I have two tables
1. Table A --> contains two columns "perid" and "categoryID" where perID is personID
2. Table B --> contains two columns "categoryID" and "name"
Lets take and example.
Table A has 3 rows
PerID CategoryID
1 1
1 2
2 1
And
Table B has 2 rows
CategoryID Name
1 Location
2 Business
The above shows that Table B has actual table and Table A displays the permission for a particular user on both the categories. If any user does not have permission for any category, then Table A does not have that row. I mean say User 1 does not have permission for CategoryID 1 then the table for Table A will be like
PerID CategoryID
1 2
2 1
Now, We have a stored procedure which is actually checking the permission for the user. It calls a function which checks the counts for both table according to category and userid and if it does not match then returns false like shown below.
SELECT count(*) from TableA where categoryid = @categoryid and personid = @userid
SELECT count(*) from TableB where categoryID = @categoryID
I know that count involves more logical reads and costs more. Did there any other way to achieve this as an optmisation point of view.
December 3, 2015 at 12:08 pm
Here's your sample data in a proper format so anyone can test a solution. Please post it this way next time.
CREATE TABLE TableA(
personid int,
categoryID int)
CREATE TABLE TableB (
categoryID int ,
name varchar(100))
INSERT INTO TableA VALUES
(1, 1),
(1, 2),
(2, 1);
INSERT INTO TableB VALUES
(1, 'Location'),
(2, 'Business');
GO
DROP TABLE TableA;
DROP TABLE TableB;
With that stated, I'm not sure why are you doing 2 counts?
Both queries should always return one or zero. If not, you have data problems and lack of proper constraints (PKs & FKs).
What are you going to input and what should you get in return?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply