Re: compare many values from two tables

richard_brust_at_yahoo.com (Richard Brust) wrote in message news:<8b15ae11.0406081314.1da4eeb3_at_posting.google.com>...
> I have two tables with 13 columns of values each - e.g. week1, week2,
> week3...week13.
>
> I want to check if any (t1.week1 > 0 AND t2.week1 > 0) or (t1.week2 >
> 0 AND t2.week2 > 0), and so on...
>
> In fact, the where clause currently looks like:
> 3 where (f.week1 > 0 and o.week1 > 0)
> 4 or (f.week2 > 0 and o.week2 > 0)
> 5 or (f.week3 > 0 and o.week3 > 0)...
>
> I also would like to be able to somehow identify the weeks in question
> - it could be week1 for part1, week4 for part2, and weeks3 and 12 for
> part3.
>
> Currently, I have a huge statement like above, where it AND's the
> similar weeks, and OR's each weekly comparison step. The table only
> has 2892 rows, and I am getting 45871 rows returned from my sql.
>
> Thanks for any assistance!!!

Richard, You mention two tables at the start of your post, are you referencing both tables in the same SQL. If so, how are your joining the tables? You are probably missing a join condition or should be using a UNION.

In general when you mix AND and OR clauses in the WHERE clause you need to group various conditions with parenthesis to prevent unexpected comparisons.
Example: where ( (f.week1 > 0 and o.week1 > 0) or (..) or (..) ) AND/OR (..) HTH -- Mark D Powell -- Received on Wed Jun 09 2004 - 18:09:28 CEST