MySQL在2张桌子上联接

本文介绍了MySQL在2张桌子上联接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要在2个并发表中进行搜索，我认为此连接可以工作，但是它给了我一个错误的语法错误.

I need to do a search on 2 simultaneous tables, and I thought that this join would work but its giving me an incorrect syntax error.

$return_arr = array(); $query = mysql_query("SELECT * FROM clients WHERE lastname LIKE '$q%' AND agencyid = '$agencyid' UNION SELECT * FROM busclients WHERE busname LIKE '$q%' AND agencyid = '$agencyid'")or die(mysql_error()); if($query) { while ($result = mysql_fetch_array($query)) { if(isset($result['busname'])){ $description['id'] = $result['ID']; $description['value'] = $result['busname'] ; array_push($return_arr,$description); } else { $description['id'] = $result['ID']; $description['value'] = $result['lastname'] . ", " . $result['firstname'] ; array_push($return_arr,$description); } } } echo json_encode($return_arr);

使用以下建议的修复程序和完整语法进行编辑

这是来自自动完成搜索框的查询.因此，当有人输入客户或企业客户名称时，它将使用此查询搜索数据库，然后使用jquery显示结果.

This is a query from an autocomplete search box. So when someone types in a client or business client name, it uses this query to search the database and then displays the results using jquery.

以下修复程序有效，但是当我在业务客户端上进行搜索时，它返回[].客户搜索工作正常.

The fix below works but when I do a search on a business client it is returning []. A client search works fine.

推荐答案

您的sql语法应如下所示:

You sql syntax shoul look like this :

SELECT * FROM clients c inner join busclients b on c.agencyid=b.idbus WHERE (c.lastname and b.busname LIKE '$q%') AND c.agencyid = '$agencyid'

只需完成此步骤，确保您阅读了本文关于如何在mysql中联接表

Just to complete the circle make sure you read this article on how to join tables in mysql