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

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.

From stackoverflow

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

  OMG Ponies : +1: This should be faster than using NOT EXISTS, see: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

• 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)
  

• 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
          )
  

• 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)
  

  docgnome : This is slightly different than what mediaslave asked for. This will select all HOST_ID where ContractYear is 2009 and it has HOST_ID has never appeared in t2 before.