sql select query name -value pair

Question

My table looks like this:

ID NAME      VALUE  
 1 home      San Jose  
 1 visitor   New Jersey  
 2 home      Chicago  
 2 visitor   Los Angeles  
 3 home      Tampa Bay   
 3 visitor   Pittsburgh  
 4 home      Montreal  
 4 visitor   N.Y. Islanders  
 5 home      Montreal  
 5 visitor   N.Y. Islanders  
 6 home      Carolina  
 6 visitor   Montreal  
 7 home      Montreal  
 7 visitor   Atlanta    

How can I come up with an sql query that will select all IDs that either have home = Montreal or visitor = Montreal but not other IDs. So in this case, we will get back 4, 5, 6, 7

Answer 1

SELECT
  ID
FROM Table
WHERE
  (Name = 'home' and Value = 'Montreal')
  OR
  (Name = 'visitor' and Value = 'Montreal')