Mysql用LIKE语句连接

Question

我有两张桌子，一张是城市的，一张是波士的。我希望从查询字符串中找到城市或pois。查询字符串可以是ABC咖啡店或旧金山。下面的查询执行，但返回空集建议？

SELECT cities.city, cities.state, pois.name FROM cities, pois 
WHERE cities.city OR pois.name LIKE '$queryString%';

表:城市

+---------+-------+---------------+
| city_id | state | city          |
+---------+-------+---------------+
|       4 | CA    | San Francisco |
+---------+-------+---------------+

表: pois

+--------+---------+-------------------------------+
| loc_id | city_id | name                          |
+--------+---------+-------------------------------+
|      2 |       4 | San Francisco Coffee Roasters |
+--------+---------+-------------------------------+

更新:基于@Johan的答案，我创建了以下查询，该查询返回以下结果。返回的结果是正确的，但我也希望使用类似于示例A查询的方法返回一个单独的行，比如示例A结果。我认为工会是我所需要的？

 SELECT p.name, c.city, c.state
FROM cities c
INNER JOIN pois p ON ( p.city_id = c.city_id )
WHERE p.name LIKE '%San Francisco%'


+-------------------------------+---------------+-------+
| name                          | city          | state |
+-------------------------------+---------------+-------+
| San Francisco Coffee Roasters | San Francisco | CA    |
+-------------------------------+---------------+-------+

示例查询：！此查询返回错误#1222 -使用的SELECT语句有不同的列数

SELECT p.name, c.city, c.state
FROM cities c
INNER JOIN pois p ON ( p.city_id = c.city_id )
WHERE p.name LIKE '%San Francisco%'
UNION SELECT c.city, c.state
FROM cities c
WHERE c.city LIKE '%San Francisco%'

示例A结果：

+-------------------------------+---------------+-------+
| name                          | city          | state |
+-------------------------------+---------------+-------+
| San Francisco Coffee Roasters | San Francisco | CA    |
+-------------------------------+---------------+-------+
+---------------+-------+
| city          | state |
+---------------+-------+
| San Francisco | CA    |
+---------------+-------+

Answer 1

SELECT DISTINCT p.name, c.city, c.state
FROM cities c
LEFT OUTER JOIN pois p ON p.city_id = c.city_id 
WHERE cities.city LIKE '%$queryString%' 
OR pois.name LIKE '%$queryString%';

或者，

SELECT p.name, c.city, c.state
FROM cities c
INNER JOIN pois p ON p.city_id = c.city_id
WHERE p.name LIKE '%$queryString%'
UNION SELECT NULL, c.city, c.state
FROM cities c
WHERE c.city LIKE '%$queryString%'

Answer 2

事先有几点意见：

·不要使用SQL '89风格的隐式连接语法，这是一种反模式。

·改用显式SQL '92联接语法，参见下面的第一个示例。

·如果$querystring来自您需要转义的用户，请参见第一个示例。

下面是示例代码：

$querystring = mysql_real_escape_string($querystring);
$query = "SELECT c.name, p.name FROM cities c 
          INNER JOIN pios p ON (p.city_id = c.id)
          WHERE c.name LIKE '%$querystring%' 
             OR p.name LIKE '%$querystring%' ";

或者你想要一个联盟

$query = "SELECT c.name FROM cities c 
          WHERE c.name LIKE '%$querystring%'
        UNION
          SELECT p.name FROM pios p 
          WHERE p.name LIKE '%$querystring%' ";

按请求编辑

如果使用union，则所涉及的所有选择都必须生成相同数量的列。

如果您没有数据，只需替换null即可。

SELECT p.name, c.city, c.state
FROM cities c
INNER JOIN pois p ON ( p.city_id = c.city_id )
WHERE p.name LIKE '%San Francisco%'
UNION 
SELECT NULL as name, c.city, c.state
FROM cities c
WHERE c.city LIKE '%San Francisco%'

Answer 3

若要通过联接执行此操作：

SELECT C.city, C.state, P.name 
FROM cities C
LEFT JOIN pois P ON P.city_id=C.id
WHERE C.city LIKE '$queryString%'
  OR P.name LIKE '$queryString%'