CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005t-sql
Control
Control

Reputation: 45

SQL filter search according to multiple column values

I am dealing with one table(3+ million rows,SQL Server) I need to filter results according to the two columns below:

<code>
...FromID| ToID  |Column5|....
...1001    2001
...1002    2020 
...1003    5000    
...1001    3000 
...2001    1001 
</code>

Now User1 can access records with FromID or ToId 1001. FromID|ToID 1001|2001 1001|3000 2001|1001

User2 can access records with FromID or ToID 1002,1003,3000 FromID|ToID 1002|2020 1003|5000 1001|3000

What is the most efficient way to do this ? Do i need to create a view for each user ?(this is working on enterprise,user count will be max 100 ) Thanks.

PS. My very first question. O.o

Upvotes: 2

Views: 1748

Answers (1)

ean5533
ean5533

Reputation: 8994

Your access criteria seem to be fairly arbitrary. User1 gets 1001, user2 gets 1002, 1003, and 3000, and I assume users 3 through 99 have arbitrary access as well. In that case, I recommend that you create a table, call it useraccess for this example:

user |accessID
---------------
user1|1001
user2|1002
user2|1003
user2|3000
...  |...

Now when you want to know what rows a user has, you can do this:

SELECT t.FromID, t.ToID, [[other columns you care about]]
FROM yourtable t
JOIN useraccess a ON t.FromID = a.accessID OR t.ToID = a.accessID
WHERE a.user = 'user2'

You can either run that query dynamically or you can create a view based on it. The usual tradeoffs between views and direct queries will apply as usual.

Edit: I just saw your note that you already have a UserRights table, so you already have step 1 completed.

Upvotes: 3

Related Questions