将两个表中的两个字段显示为mysql LIKE中的一个字段

I want to implement like search for autocomplete.I have two tables,Location and SubLocation.

我想像搜索autocomplete一样实现。我有两个表,Location和SubLocation。

I want to return only one field for this like.Here is my query

我想只为此返回一个字段。这是我的查询

SELECT l.loc_name,sl.sub_loc FROM Location l,SubLocation sl 
 where l.loc_name LIKE '$term%' OR sl.sub_loc='$term%'

I want to show matching result from both tables as one return.EG,if i type D so i can view Dubai from first table and DubaiMarina from second table as one coloumn

我希望将两个表的匹配结果显示为一个return.EG,如果我输入D,那么我可以从第一个表中查看迪拜,从第二个表中查看DubaiMarina作为一个coloumn

1 个解决方案

#1

You can use UNION

你可以使用UNION

SELECT l.loc_name FROM Location l
 where l.loc_name LIKE '$term%' 
UNION
SELECT sl.sub_loc FROM SubLocation sl 
 where sl.sub_loc='$term%'

If the tables do not have duplicates, you can replace UNION with UNION ALL as the union all option will not look for duplicates, it might be a little faster depending on the amount of data in returned by the queries.

如果表没有重复项,则可以将UNION替换为UNION ALL,因为union all选项不会查找重复项,它可能会更快一些,具体取决于查询返回的数据量。