Search if values X AND Y exist in columns A OR B [SQL]

user_10661539 0
Viewed 47

I have a table of soccer match data, among many other columns I have columns containing the team Ids for the home team and the away team.

Often times I want to find all matches between two teams I need to construct a query that looks like

(`home_id` = X AND `away_id` = Y) OR (`home_id` = Y AND `away_id` = X)

This works fine, but ideally I could shorten this query and remove the OR and second and clause.

Is this something mySQL can handle, and is there a limit to the number of columns this can apply to?

mysql sql
1 Answers

You could use tuples:

where ('X', 'Y') in ( (home_id, away_id), (away_id, home_id) )

or:

where (home_id, away_id) in ( ('X', 'Y'), ('Y', 'X') )
user_11440350
Related