SQL: Query 2 columns; if one is valid then ignore the other

Question

Hello,

I'm not sure how to approach this SQL statement so please advise, even if its just a pointer to something I can read up on

I have a table, such as the following

ID        OVER60      OVER80
1          N           N
2          Y           N
3          Y           Y

The IDs are unique, what I need to do is to create a SELECT statement that will return the ID, and then either 'Over 60' or 'Over 80', such as :

ID        AGE
2         Over 60
3         Over 80

What I need to avoid, is duplicates, meaning if someone is 'Over 80', then there is no need to display 'Over 60' aswel, since its pretty obvious

I've looked at UNION to see if that would help, and this is what I have so far :

  SELECT 
    a.id,
    'OVER60' as AGE
  FROM
    MY_TABLE a
  WHERE
    a.OVER_60 != 'N'
UNION
  SELECT 
    a.id,
    'OVER80' as AGE
  FROM
    MY_TABLE a
  WHERE
    a.OVER_80 != 'N'

    ;

What are my options? would using a MINUS help me here?

Thanks

Answer 1

You should be able to use a CASE STATEMENT for this.

Something ike

SELECT a.ID,
        CASE    
            WHEN a.OVER_80 = 'Y' THEN 'OVER80'
            WHEN a.OVER_60 = 'Y' THEN 'OVER60'
        END
FROM    MY_TABLE
WHERE   a.OVER_60 = 'Y'

Answer 2

CASE, IF, COALESCE - various approaches are possible here. A lot depends on what database server you use ;)

Answer 3

The right way to do this, if you want to scale well, is to re-engineer your table. Per-row functions like case and coalesce do not scale.

Don't store whether they're over 60 or 80 in the database at all. For a start, that's a variable thing changing as time goes by. Store their birthdate which is an invariant.

Then simply run:

select a.id, 'over 60' from my_table
    where birthdate < now() - 60 years
    and   birthdate >= now() - 80 years
union all select a.id, 'over 80' from my_table
    where birthdate < now() - 80 years;

With an index on birthdate, that should scream along because, unless the DBMS is brain dead, now() - N years will only be calculated once.