Re: Please help me with a simple query!!!

grohrer_at_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 Received on Thu Dec 15 2005 - 22:34:03 CST