Mysql按名称从不同的表中选择

Question

I have the following 2 tables inside MySQL:

Table Settings

|id |name|clientid|
------------------
| 1 | a  | 33    |
------------------
| 2 | b  | 34    |
------------------

Table Client

|id  |clientname |
------------------
| 33 | c         |
------------------
| 34 | a         |
-----------------

Where I am trying to make a Search query where it will always return the id from the first table.

Query so far:

$this->conn->prepare("SELECT Settings.id as value, 
                             Settings.name as label 
                      FROM Settings
                      LEFT JOIN client ON Settings.clientid = client.id
                      WHERE Settings.name LIKE :keyword 
                            OR Settings.id LIKE :keywordid 
                            OR client.clientname LIKE :keywordclient 
                      LIMIT 10");

$stmt->bindValue(':keyword', "%{$this->keyword}%", PDO::PARAM_STR); 
$stmt->bindValue(':keywordid', "%{$this->keyword}%", PDO::PARAM_STR);   
$stmt->bindValue(':keywordclient', "%{$this->keyword}%", PDO::PARAM_STR);   

So basically the idea is the following If Search for letter a the search will do search inside Settings to see if keywrod is LIKE id, or name, or it will have to search and see inside the client table and see if it is like Clientname. The result will always have to be the Id from Settings table and than the name or client name as label from ther other two pages depending on the result.

And in this case the results have to be two

value: 1 label: a
value: 2  /*from Settings table*/ label: a  /*from the client table*/

Any help will be appreciate solving this situation.

Answer 1

One way is to utilize UNION, by breaking the problem into two SELECT queries. First query would only search in the Settings table; while the second one would do the same in the Client table only. This approach has an added advantage of converting a less-efficient LEFT JOIN to INNER JOIN.

The SQL query will be:

(SELECT id   AS value,
        name AS label
 FROM   settings
 WHERE  name LIKE :keyword
         OR id LIKE :keyword
 LIMIT  10)
UNION
(SELECT s.id   AS value,
        c.name AS label
 FROM   settings AS s
        JOIN client AS c
          ON c.id = s.clientid
 WHERE  c.clientname LIKE :keyword
 LIMIT  10)
ORDER  BY value
LIMIT
10 

Note: Since you want only 10 rows, I have put LIMIT 10 inside both the SELECT queries, to optimize the data being selected for union later.