"Hexman" <Hex***@Binary.com> wrote in message
news:hfef52d8nbkni1gm1talu6jjodombf9bfs@4ax.com...
> 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.