需要在一个字段Mysql中搜索两个值

Question

I need search for two values in the same field.

╔════╦════════════╦══════════════╗
║ ID ║ Meta_key   ║ Meta_value   ║
╠════╬════════════╬══════════════╣
║  1 ║ first_name ║    pritesh   ║
║  2 ║ last_name  ║    mahajan   ║
║  3 ║ first_name ║    ritesh    ║
║  4 ║ last_name  ║    jain      ║
║  5 ║ first_name ║    john      ║
║  6 ║ last_name  ║    a         ║
║  7 ║ first_name ║    Mambo     ║
║  8 ║ last_name  ║    Nombo     ║
╚════╩════════════╩══════════════╝  

This is my table and I want to search all the first names and last names. Below is my query but this does not return what I want.

SELECT * 
FROM `wp_usermeta` 
WHERE `meta_key` = 'last_name' 
  AND `meta_value` LIKE  '%mahajan%' 
  AND `meta_key` = 'first_name' 
  AND `meta_value` LIKE  '%a%'; 

Answer 1

Here you go

SQL Fiddle

Query:

SELECT first_name, last_name
FROM 
  (
    (
      SELECT meta_value AS first_name, id AS a_id 
      FROM wp_usermeta 
      WHERE meta_name = 'first_name'
    ) as a
  )
JOIN
  (
    (
      SELECT meta_value AS last_name, id AS b_id 
      FROM wp_usermeta 
      WHERE meta_name = 'last_name'
    ) as b
  )
WHERE a_id = b_id-1

Results:

| FIRST_NAME | LAST_NAME |
|------------|-----------|
|    pritesh |   mahajan |
|     ritesh |      jain |
|       john |         a |
|      Mambo |     Nombo |

With this, you can easily search using the two virtual columns first_name and last_name. You just need to add additional conditions to the WHERE clause. something like this:

  WHERE a_id = b_id-1
  AND first_name LIKE "%tesh%"
  AND last_name LIKE "%jai%"

will produce

| FIRST_NAME | LAST_NAME |
|------------|-----------|
|     ritesh |      jain |

NOTE

The big assumption I have made here (based on your sample data) is that the related first_name and last_name are always going to have successive id's. (two successive records in the table).