Selecting Reords Without the 'OR' Condition (If Possible)

[Auguy]

I have a SQL 2000 database that is shared by two offices. In one of the tables I have a column called company that normally has a One or a Two in it to indicate the different offices. For almost all of the records each office cannot see the other office's records. For the records that are "Shared", I change the company code to a negative number. This lets me write a select statement something like

Select Col1, Col2, ... From Table1 Where (Abs(Company) = @UserCompany Or Company < 0) And ...

I'm thinking of moving the Shared indicator (Negative Company Value) to a bit column. This will help simplify some of the other coding, but I would still need something like

Select Col1, Col2, ... From Table1 Where (Company = @UserCompany Or Shared = 1) And ...

I would like to eliminate the OR condition, but not sure how to go about it. At this time I'm not seeing a performance problem. The table only has about 200K records and grows by about 50K a year. I would like to accomplish this using the "Best" method in case I encounter millions records down the road. Any suggestions would be welcome.

Auguy
Sylvania/Toledo Ohio

 

[simian336]

Unless there are more companies in the file that you have not mentioned they you do not need the "Company" or shared condition.

Select Col1, Col2
From Table1

is going to give you both results.

Simi

 

[lameid]

Simian336,
You are missing the OP point... Company 1 should see the shared records and it's own but not company 2's.

Auguy,
Unless it makes sense that the shared records belong in their own table, you are stuck with the or criteria. Like any query, ensuring indexes are appropriate is what is most important. If you were still designing your tables, I might suggest you approach it more from a permission / membershhip structure so that if another office is added you would be ready to go. Then you might assign a role or group to a record and then offices or whatevers to the role... Then you are getting the records based on one criteria and a join. It is much more flexible that way but not necessarily faster or slower.