Re: compare many values from two tables

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0406090809.4a8cecd0_at_posting.google.com>...
> 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 --

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! Received on Thu Jun 10 2004 - 00:38:35 CEST