Simple SQL Query Help - Return Rows Where Criteria Matches ALL

Question

How do I construct my query to return only the values that match a, b, AND c? For example, I would like to return all companies that have financial data with a fiscal year of 2007, 2008, and 2009.

SELECT Company from Table WHERE FiscalYear IN (2007,2008,2009) gives me all the companies in which any of the 3 years exists. I need to find those companies that have data in all three years.

Thanks!

Answer 1

SELECT Company 
from Table 
WHERE CompanyID in (
    select CompanyID 
    from Table
    WHERE FiscalYear in (2007,2008,2009) 
    group by CompanyID
    having count(distinct FiscalYear) = 3
)