compare many values from two tables

[Richard Brust]

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!!!

[Mark D Powell]

ri***********@yahoo.com (Richard Brust) wrote in message news:<8b**************************@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 --

[Richard Brust]

Ma*********@eds.com (Mark D Powell) wrote in message news:<26**************************@posting.google. com>...

ri***********@yahoo.com (Richard Brust) wrote in message news:<8b**************************@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 --

Mark -

You are right in both cases:

+ I am using a UNION, and
+ I added second set of parens surrounding the entire AND/OR block,
and it worked fine. Your 'Example:' above is exactly how it looks
now, and returns the expected rows.

Thanks for taking the time to post!

[Mark D Powell]

ri***********@yahoo.com (Richard Brust) wrote in message news:<8b**************************@posting.google. com>...

Ma*********@eds.com (Mark D Powell) wrote in message news:<26**************************@posting.google. com>...

ri***********@yahoo.com (Richard Brust) wrote in message news:<8b**************************@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 --

Mark -

You are right in both cases:

+ I am using a UNION, and
+ I added second set of parens surrounding the entire AND/OR block,
and it worked fine. Your 'Example:' above is exactly how it looks
now, and returns the expected rows.

Thanks for taking the time to post!

Glad to have been of help. -- Mark --