SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Sep 2003
    Location
    Northern California
    Posts
    605
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query quandary : not equal to

    ***Don't hesitate to point me to the manual ...if you know where the answer is. I could not find what I needed.***

    I have a table with 7 columns, 3 of which are 2006, 2005, 2004.

    I want to return rows in which both '2005' is not = 0.00 and 2006 is not = 0.00

  2. #2
    SitePoint Guru Rob_D's Avatar
    Join Date
    Oct 2006
    Location
    UK
    Posts
    882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The answer may be here
    It has yet to be proven that intelligence has any survival value.
    Arthur C. Clarke

  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT `col1`, `col2`, ... FROM `table` WHERE `2005` != 0 AND `2006` != 0
    Sounds like bad design though, both in having columns for each year and naming them after the year. You might consider a more flexible design:

    Code:
    SOME_DATA_TABLE
    -------------------
    id: int, pk
    col1
    col2
    ...
    
    YEARLY_VALUES
    ----------------
    data_id: int, fk
    year_name: int, unique
    value: double

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i agree with dan, you should normalize that yearly data into a separate table

    okay dan, how about the sql for the normalized table, to find ids which have non-zero values for both 2005 and 2006?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    First thing that comes to mind is to use two subqueries... I'm sure it can be written as a join or two which would be faster...

    Code:
    SELECT
      id, col1, col2 ...
    FROM `some_data_table`
    WHERE id IN (
      SELECT data_id FROM `yearly_values`
      WHERE year_name = 2005 AND value > 0)
    AND id IN (
      SELECT data_id FROM `yearly_values`
      WHERE year_name = 2006 AND value > 0)

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    not bad

    i would use a single subquery, and count the number of matched rows
    Code:
    SELECT id
         , col1
         , col2 ...
      FROM `some_data_table`
     WHERE id IN 
           ( SELECT data_id 
               FROM `yearly_values`
              WHERE year_name IN ( 2005, 2006)
                AND value > 0
             GROUP
                 BY date_id
             HAVING COUNT(*) = 2 )
    this is easier to extend to 3 or more years

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •