Re: Handling large number of OR/IN conditions

On May 1, 2009, at 10:49 AM, David Wall wrote:

> We have a database report function that seemed clean when the number
> of users was small, but as the number of users grow, I was wondering
> if anybody had any good ideas about how to handle OR or IN for
> SELECTs.
>
> The general scenario is that a manager runs reports that list all
> records that were created by users under his/her oversight. So,
> when the number of users is small, we had simple queries like:
>
> SELECT field1, field2 FROM table1 WHERE creator_user_id = 'U1' OR
> creator_user_id = 'U2';
>
> But when there are thousands of users, and a manager has oversight
> of 100 of them, the OR construct seems out of whack when you read
> the query:
>
> WHERE creator_user_id = 'U1' OR creator_user_id = 'U2' ... OR
> creator_user_id = 'U99' OR creator_user_id = 'U100'
>
> I know it can be shortened with IN using something like, but don't
> know if it's any more/less efficient or a concern:
>
> WHERE creator_user_id IN ('U1', 'U2', ...., 'U99', 'U100)
>
> How do people tend to handle this sort of thing? I suspect manager
> reports against their people must be pretty common. Are there any
> good tricks on how to group users like this? Unfortunately, group
> membership changes over time, and users may report to more than one
> manager and thus belong to more than one group, so we can't just
> have a 'creator_group_id' attribute that is set and then query
> against that.

Sounds like a job for a two column table that lists manager and report.

select table1.field1, table2.field2 from table1, reports where
table1.creator_user_id = reports.peon and reports.overlord = 'bob'

Cheers,
Steve