我的数据库上有三个表，如下所示：
kill_list
+-----------------------------------------------+
| id | batch_name | execution_date | status |
+-----------------------------------------------+
| 1 |Shallow Minds | 2013-10-29 | 0 |
+-----------------------------------------------+
| 2 | Brain Dead | 2013-10-26 | 1 |
+-----------------------------------------------+
kill_list_designation
+------------------------------+
| id | kill_list | target |
+------------------------------+
| 1 | 1 | 1 |
+------------------------------+
| 2 | 1 | 2 |
+------------------------------+
| 3 | 2 | 4 |
+------------------------------+
| 4 | 1 | 5 |
+------------------------------+
| 5 | 2 | 3 |
+------------------------------+
targets
+--------------------------------+
| id | firstname | lastname |
+--------------------------------+
| 1 | John | Smith |
+--------------------------------+
| 2 | Mark | Hall |
+--------------------------------+
| 3 | Kim | Stone |
+--------------------------------+
| 4 | Rob | Reeds |
+--------------------------------+
| 5 | Ivan | Gay |
+--------------------------------+
现在，我只能使用此查询对kill_list表进行查询搜索
SELECT *
FROM kill_list
WHERE batch_name LIKE CONCAT('%', 'query term', '%')
OR execution_date LIKE CONCAT('%', 'query term')
OR status LIKE CONCAT('%', 'query term', '%');
在此查询中，我只能获取杀戮列表的详细信息，而不能获取列表中包含的目标。如果我搜索"标记"，查询应该为我提供包含 Mark 的 #1 kill_list的详细信息。
如何通过输入术语来获取击杀名单中的所有目标？查询必须能够将术语与目标的名称进行比较。非常感谢！
您必须为其创建 Join。
此结构的简单联接如下所示：
SELECT kl.*, kld.*,t.* FROM kill_list as kl,kill_list_designation as kld,targets as t
WHERE
kl.id = kld.kill_list
AND
kld.target = t.id
要专门搜索标记，您可以使用
SELECT kl.*, kld.*,t.* FROM kill_list as kl,kill_list_designation as kld,targets as t
WHERE
kl.id = kld.kill_list
AND
kld.target = t.id
AND
t.firstname like 'Mark'
SQL FIDDLE
像这样尝试
SELECT *
FROM kill_list A
JOIN kill_list_designation B ON A.id=B.kill_list
JOIN targets C ON B.target=C.id
WHERE A.batch_name LIKE CONCAT('%', 'query term', '%')
OR A.execution_date LIKE CONCAT('%', 'query term')
OR A.status LIKE CONCAT('%', 'query term', '%')
OR C.firstname LIKE CONCAT('%', 'query term', '%')
OR C.laststname LIKE CONCAT('%', 'query term', '%');