Search code examples
sqlselectrelational-division

SQL- all rows which are present in all years

I have the following columns in my DB

Codes

year |  code   |
----------------
2009 | 10001   |
2009 | 10002   |
2009 | 10003   |

2010 | 10001   |
2010 | 10002   |
2010 | 10004   |

2011 | 10001   |
2011 | 10005   |
2011 | 10010   |

How do I find all the codes which are preset in all years. For example in the above I want only 10001 since its the only code which is present in all years. I've tried 

SELECT code FROM Codes
GROUP BY code 
HAVING count(year) = 3

But this isn't very flexible since I may want to restrict this to a subset, for example, find all codes only in years 2010 and 2011.

Solution 
  • SELECT code 
FROM Codes
where year in (2010,2011,2012)
GROUP BY code 
HAVING count( distinct year ) = 3