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')

Answer 2

Select ID from Table where Name = 'home' and Value = 'Montreal'
UNION ALL
Select ID from Table where Name = 'visitor' and Value = 'Montreal' 

Answer 3

If 'home' and 'visitor' are the only options, which would seem to be true in a case like the example given where you've got games between two sports teams, then simply:

select ID
    from YourTable
    where Value = 'Montreal'

Answer 4

Assuming that the name column holds values other than just "home" and "visitor" - it's better to be explicit to make sure you get the records you want:

SELECT t.id
  FROM YOUR_TABLE t
 WHERE t.name IN ('home', 'visitor')
   AND t.value = 'Montreal'

...otherwise, there's no value including the filtration in the query:

SELECT t.id
  FROM YOUR_TABLE t
 WHERE t.value = 'Montreal'