grohrer@gmail.com wrote:

(oh no, top posting!)

: I DO know the widgets I want to return, for example

: Dept Product
: 01 002312
: 02 023124
: 01 002315
: 05 002312
: ...

: As you can see, there are two product numbers with 002312 but each has
: a different department. In all I have a hundred or more KNOWN widgets
: that I want to return.

: My question is how can I build an effecient query against a table when
: I have about a hundred or so items like the above, where I need to
: query against the department number AND product number.

: Do I do the long where clause for about a hundred of these..
: ..
: WHERE (dept = '01' AND product = '002312) OR (dept = '02' AND product =
: '023124') OR...

: Or is there a better way? The above example will make for a REALLY
: long query string.

You could put them in a table and then use various techniques to make use
of the table.

-- _NOT_ valid sql, just for illustration

create table valid_combos (Dept, Product);
insert into valid_combos (Dept, Product) values ('01','002312');
insert into valid_combos (Dept, Product) values ('02','023124');
... etc...

You could then join with that table

select *
from
main_table , valid_combos
where
main_table.Dept = valid_combos.Dept
and main_table.Product = valid_combos.Product
and other conditions to filter the result

or use something like this (but lookup the syntax cause I might have it
wrong

select *
from
main_table
where (Dept,Product) in (select Dept,Product from valid_combos)


or

select *
from
main_table mt
where
exists (select *
from valid_combos vc
where mt.Dept = vc.Dept
and mt.Product = vc.Product
)

and probably others as well