SQL Filtering

From: Ben Heath <ben_at_benheath.com>
Date: Fri, 2 Aug 2002 08:25:18 -0500
Message-ID: <vLv29.38$ip1.13382@news.uswest.net>

I have a problem, that I'm sure someone has had to solve before. I have a client table and I want to allow users access to only certain classes of clients. In my example I have 'OrdinaryClients' and 'ImportantClients'. If I want to filter a query so that certain users can only see
'OrdinaryClients' what's the best way to do this.

This is what I am currently doing, but the queries are very slow as the table sizes increase. When an index is added to Client.clientNumber it's not being used if the resultset returned from Userprofile is larger than around 10 to12 clients. Any ideas?
Table:

Client

---

clientNumber varchar(6)
clientName varchar(20)
clientDesc varchar(60)
otherClientInfo varchar(120)

.
.
.

UserProfile

---

profileName varchar(20)
clientNumber varchar(6)

.
.
.

User

---

userName varchar(20)
password varchar(20)
profileName varchar(20)

.
.
.

User

---

'ben', 'xxxx', 'OrdinaryClients'

UserProfile

---

'ImportantClients', '111111'
'ImportantClients', '222222'
'ImportantClients', '333333'
'ImportantClients', '444444'
'OrdinaryClients', '555555'
'OrdinaryClients', 666666'
'OrdinaryClients', '777777'

Client

---

'111111', 'abc Company', ...
'222222', 'def Company', ...
'333333', 'ghi Company', ...
'444444', 'jkl Company', ...
'555555', 'mno Company', ...
'666666', 'pqr Company', ...
'777777', 'stu Company', ...

select clientName from Client where client.clientNumber in (select clientNumber from userProfile where profileName = 'OrdinaryClients') Received on Fri Aug 02 2002 - 08:25:18 CDT