Re: compare many values from two tables

richard_brust_at_yahoo.com (Richard Brust) wrote in message news:<8b15ae11.0406091438.3b5931c4_at_posting.google.com>...
> 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!

Glad to have been of help. -- Mark -- Received on Fri Jun 11 2004 - 15:50:01 CEST