MySQL: Get all records that have an id for one year but not another
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.
select
HOST_ID
from
table_name
where
ContractYear = 2009
AND HOST_ID NOT IN (select HOST_ID from table_name where ContractYear = 2008)
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)
精彩评论