Can anyone suggest an easier or better way to skin this cat? Two Select Queries

  • Thread starter Thread starter Hexman
  • Start date Start date
H

Hexman

Hi All,

Here's my situation. I have two tables. Table1 is a 'transaction'
table. Table2 is a 'master' table. What I want to do is two-fold.
First I want to check if there is a match between ProdGrp (t1ProdGrp =
t2ProdGrp). If there isn't a match, indicate on the transaction there
is a critical error.

If there is a match, check to see if there is a complete match
(t1ProdGrp = t2ProdGrp AND t1Cat = t2Cat AND t1subCat = t2SubCat). If
there is a full match flag the transaction as valid. If not a full
match, indicate on the transaction there is a minor (correctable)
error.

My original thought was to create 2 select statements and do 2 queries
against Table2.

"Select * from Table2 where t1ProdGrp = t2ProdGroup "

"Select * from Table2 where t1ProdGrp = t2ProdGroup AND _
t1Cat = t2Cat AND t1subCat = t2SubCat"

That seems quite expensive in terms of processing. (Maybe it isn't)

The tables have the following key and field structure:

Table1
PK t1Date
pk t1Part
pk t1WH
t1ProdGrp
t1Cat
t1SubCat
....other fields.....

Table2
PK t2ProdGrp
pk t2Cat
pk t2SubCat
....other fields.....


Can anyone suggest an easier or better way to skin this cat?

Thanks,

Hexman

P.S. There are about 40 ProdGrps, each of the ProdGrps have 20 Cats
and each Cat has 3 - 20 SubCats.
 
It is only expensive when there is a problem. Assume you are finding the
actual depth of the problem only when there are problems, so I am not overly
concerned, esp. if you have proper indexing, et al.

You could combine the two and set flags to determine which one it is, but
that would get more expensive as you introduce an OR. In this type of query,
it is difficult to filter the work down using some form of temp table, so
you are going to have to take the expense. The fact you are finding problems
first and only examining the depth of the problem when there are results is
a good sign.

One thing you might do is make your first query be a condition.

IF EXISTS (SELECT * FROM table2 WHERE t1ProdGrp = t2ProdGroup)

That is a very inexpensive statement compared to returning rows. Since you
are going to query based on the answer above, you can either return data or
not depending on that answer. That would reduce the expsense.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************
 
Thanks Cowboy,

Just want to be sure I'm not doing something that will kill processing
or defy logic.

Will do the "If EXISTS".

Hexman
 
Back
Top