MySQL: Get all records that have an id for one year but not another

Question

Hi,

I have a table:

HOST_ID | ContractYear
1       | 2008
2       | 2008
3       | 2008
1       | 2009
4       | 2009

What I need is a query that will tell me all the HOST_ID's that are in 2009 that are not in 2008.

The answer in the example above is HOST_ID=4.

Thanks.

Answer 1

select
  HOST_ID
from
  table_name
where
  ContractYear = 2009
  AND HOST_ID NOT IN (select HOST_ID from table_name where ContractYear = 2008)

Answer 2

Use:

SELECT t.host_id
  FROM TABLE t
 WHERE t.contractyear = 2009
   AND NOT EXISTS(SELECT NULL
                    FROM TABLE t2
                   WHERE t2.host_id = t.host_id
                     AND t.contractyear = 2008)

Answer 3

SELECT  DISTINCT host_id
FROM    mytable mo
WHERE   ContractYear = 2009
        AND NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi
        WHERE   mi.host_id = mo.host_id
                AND ContractYear = 2008
        )

Answer 4

more generic because it filters all out which are not the year you are looking for

select HOST_ID
from table t
where ContractYear = 2009
and HOST_ID not in (select distinct HOST_ID
                    from table t2
                    where t.ContractYear != t2.ContractYear)