Query with multiple IN-statements but without the cartesian product
- by Janne
How could I make this kind of query e.g. in MySQL
SELECT * FROM Table t
WHERE t.a IN (1,2,3)
AND t.b IN (4,5,6)
AND t.c IN (7,8,9) ...
so that the result would contain only the three rows:
t.a|t.b|t.c
---+---+---
1 | 4 | 7
2 | 5 | 8
3 | 6 | 9
The above query of course returns all the combinations of the values in the IN clauses but I would like to get just the ones where the first elements of each tuple match, second elements of each tuple match and so on.
Is there any efficient way to do this?
By the way is there some common term for this kind of query or concept? I'm having hard time coming up with the question's title because I can't put this into words..