deitch deitch - 1 month ago 5
MySQL Question

How do I select records in MySQL with multiple columns matching map of values?

I have the following 3-column table:


+----+---------+------------+
| ID | First | Last |
+----+---------+------------+
| 1 | Maurice | Richard |
| 2 | Yvan | Cournoyer |
| 3 | Carey | Price |
| 4 | Guy | Lafleur |
| 5 | Steve | Shutt |
+----+---------+------------+


If I want to look for everyone in
(Maurice,Guy)
I can do
select * from table where first in (Maurice,Guy)
.

If I want to find just
Maurice Richard
, I can do
select * from table where first = "Maurice" and last = "Richard"
.

How do I do a map, an array of multiples?


[
[Maurice, Richard]
[Guy,Lafleur]
[Yvan,Cournoyer]
]


If I have an arbitrary number of entries, I cannot construct a long complex
where (first = "Maurice" and last = "Richard") or (first = "Guy" and last = "Lafleur") or ....
.

How do I do the moral equivalent of
where (first, last) in ((Guy,Lafleur),(Maurice,Richard))
?

Answer

You can do it just like you describe it:

SELECT *
FROM mytable
WHERE (first, last) IN (('Guy','Lafleur'),('Maurice','Richard'))

Demo here

Comments