Table Joining

[matt72]

I'm not sure if I'm having a mental blank or this is harder than it appears... Either way, if someone could help me out that'd be great!

I've got two simple tables:

SiteTable:
SiteID SiteName
1 Site1
2 Site2
3 Site3

&

CategoryTable:
SiteID CategoryName
1 Dogs
1 Cats
2 Cats
2 Birds
3 Cats

What I'm trying to do is select all sites from SiteTable that have the category Cats (in CategoryTable), but not the category Dogs. So in this instance site 2 and 3 will be in the result set...

Thanks in advance for any tips you can give me 🙂

[Lars_Berg]

select siteName 
  from SiteTable
 where not exists 
     (select *
       from categoryTable
      where categoryTable.SiteID = SiteTable.SiteID
         and categoryTable.CategoryName = 'Dogs')
    and SiteID in
    (select SiteID
        from CategoryTable
       where CategoryName = 'Cats')

[matt72]

Thanks a lot Lars, that works well!