SQL Server - selecting data where not exists

Question

I need a way to be able to select from a table if that table doesn't contain data of a certain type.

For example if we have a table called farm and another table called animal. Now FarmA contains a pig and a goat and FarmB just contains a goat. I want to select all farms that contain no pigs.

My first try was to do this:
SELECT f.* FROM farm f INNER JOIN animal a ON f.Id = a.FarmId WHERE a.Name <> 'pig';
But this still returns me back FarmA because it contains a goat but I don't want it to return back any farms that have pigs.

I've tried some subquerys and used not exists but that didn't work either. I'm sure this is easy I just can't structure my query right.

Answer 1

Does this work? I'm more familiar with Oracle but I checked and the "NOT EXISTS" syntax seems to be the same:

SELECT f.* FROM FARM f WHERE NOT EXISTS 
  (SELECT * FROM animal a WHERE a.FarmId = f.FarmId and a.Name = 'pig')

Answer 2

select f.* 
from Farm f 
left outer join (
    select FarmID 
    from Animal 
    where Name = 'pig'    
) a on f.ID = a.FarmID 
where a.ID is null