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 Montr开发者_运维技巧eal
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
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'
SELECT
ID
FROM Table
WHERE
(Name = 'home' and Value = 'Montreal')
OR
(Name = 'visitor' and Value = 'Montreal')
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'
Select ID from Table where Name = 'home' and Value = 'Montreal'
UNION ALL
Select ID from Table where Name = 'visitor' and Value = 'Montreal'
精彩评论