query quandary : not equal to

[mgm_03]

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

[Rob_D]

The answer may be here

[Dan Grossman]

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:

SOME_DATA_TABLE
-------------------
id: int, pk
col1
col2
...

YEARLY_VALUES
----------------
data_id: int, fk
year_name: int, unique
value: double

[r937]

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?

[Dan Grossman]

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

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)

[r937]

not bad

i would use a single subquery, and count the number of matched rows

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