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

grohrer_at_gmail.com wrote:
> I have a table which contains information regarding widgets. Each
> widget has a Department Number and a Product Number.
>
> I need to query the table for a specific set of widgets...about 100 in
> all.
>
> I'm familiar with the basic queries such as:
>
> SELECT *
> FROM projectTable
> WHERE prodNum IN ('002312','023124','254124')
>
> which works great if your only querying with a single specific field.
>
> The only other method I know if is to do the following:
>
> SELECT *
> FROM projectTable
> WHERE (deptNum = '01' AND prodNum = '002312') OR (deptNum = '02' AND
> prodNum = '002314') OR .......
>
> Using this method for a hundred widgets would make for a pretty sizable
> query string.
>
> Is there another method which is better and effecient?
>
> Thanks in Advance!
>

Yes:

1) Don't call identifiers numbers
2) create a department table
3) create a product number
4) rewrite the query as a joins over three tables

Any IN list with over x elements means a poorly analyzed application: a reference table is missing. Define x to your liking, but in my opinion, x should be less than 10.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...