Ive got a table containing results for a certain sport competition from 2009 - present. The table contains numerous columns such as hometeam, awayteam, homescore, awayscore etc. As you can see from the image below:
If I want to display results for a certain team (in this example team Sharks) for both home and away matches I do it like so:
SELECT *
FROM `results`
WHERE `hometeam` = 'Sharks' || `awayteam` = 'Sharks'
The above code is working fine and I get the correct results as you can see from the image:
The problem
The problem im having is when I want to display BOTH home and away match results for only two SPECIFIC teams, im not getting the correct results. (In the query below im trying to only display home and away results when team Stormers played against team Sharks) I query the table as follows:
SELECT *
FROM `results`
WHERE `hometeam` = 'Stormers' || `awayteam` = 'Stormers' && `hometeam` = 'Sharks' || `awayteam` = 'Sharks'
You can see from the image below that the above query is returning the wrong results. The query is returning results for both team Stormers and team Sharks for ALL their home and away matches against ALL opponents
The question
Im looking for a way where the query will only return results for specific teams when they played against each other home and away. Example: for this query I only wanted to display all results for team Stormers against team Sharks, ignoring all other opponents.
If anyone can be so kind to point me in the right direction it would be greatly appreciated. Thank you in advance.