compare many values from two tables

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Richard Brust
    #1

    compare many values from two tables

    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!!!
    Tags: None
    • Mark D Powell
      #2
      Re: compare many values from two tables

      richard_brust@y ahoo.com (Richard Brust) wrote in message news:<8b15ae11. 0406081314.1da4 eeb3@posting.go ogle.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 --

        Comment

        • Richard Brust
          #3
          Re: compare many values from two tables

          Mark.Powell@eds .com (Mark D Powell) wrote in message news:<2687bb95. 0406090809.4a8c ecd0@posting.go ogle.com>...
          richard_brust@y ahoo.com (Richard Brust) wrote in message news:<8b15ae11. 0406081314.1da4 eeb3@posting.go ogle.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!

            Comment

            • Mark D Powell
              #4
              Re: compare many values from two tables

              richard_brust@y ahoo.com (Richard Brust) wrote in message news:<8b15ae11. 0406091438.3b59 31c4@posting.go ogle.com>...
              Mark.Powell@eds .com (Mark D Powell) wrote in message news:<2687bb95. 0406090809.4a8c ecd0@posting.go ogle.com>...
              richard_brust@y ahoo.com (Richard Brust) wrote in message news:<8b15ae11. 0406081314.1da4 eeb3@posting.go ogle.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 --

                Comment

                Previous template Next
                Working...