Question 195127 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to query with multiple fields in IN clause?

I want to query a table with multiple combined fields in an IN clause.

Example: find all records with "john doe" and "marc smith", but ignore all other persons..

SELECT * FROM persons 
WHERE firstname IN ('john', 'marc')
   AND lastname IN ('doe', 'smith');

But I don’t want to find jane;doe for example. But how can I combine multiple columns inside the IN clause?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I discovered mysql supports this out of the box:

    SELECT * FROM persons WHERE (firstname, lastname) IN (('john', 'doe'), ('marc', 'smith'));

  2. 0

    You might consider creating a sort of lookup table that contains the first- and lastname combinations you’re interested in.
    Then query something like this:

    SELECT persons.*
FROM   persons
JOIN   my_lookup
  ON   persons.firstname = my_lookup.firstname
 AND   persons.lastname = my_lookup.lastname;

    In order to speed up the process, I would create a unique index on the first/lastname within that lookup table.

    Login or Signup to reply.
  3. 0

    You can just use the above concat function which would help you to eliminate different combination of name and surname.
    Hope this helps !!!!

     select * from persons where CONCAT(first_name,' ',last_name) in ("john doe","marc smith");
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search