MySQL在2张桌子上联接 [英] MySQL join on 2 tables

问题描述

我需要在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

这篇关于MySQL在2张桌子上联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！