SELECT * FROM table WHERE * LIKE (SELECT * FROM table WHERE

Question

我有一个单独的城市表，其中包含城市代码，还有一个包含列"address"的主表，该列只是一个短地址。我想要的是用与"地址"列中的数据相似的代码选择城市。

$cityCode=$db->query("SELECT city AS bot FROM city_table WHERE city LIKE (SELECT address FROM people WHERE people_id = $zz)");
$cityCode=$cityCode->num_rows > 0 ? $cityCode->fetch_array()['bot'] : "NOT LIKE";
city_table:
| province                | city                     |
| ----------------------- | ------------------------ |
| ILOCOS NORTE/012800000  | CITY OF BATAC/012805000  |
people:
| people_id               | address                  |
| ----------------------- | ------------------------ |
| 1                       | P-2, Brgy. 20, Batac City|
If the address contains "Batac", I want to echo it as 'CITY OF BATAC/012805000'

如何做到这一点？

Answer 1

在where子句中使用Exists，如下所示：

SELECT city AS bot FROM city_table c 
WHERE EXISTS  (SELECT 1 FROM people WHERE people_id = $zz AND address LIKE '%'+c.city+'%' )

Answer 2

您需要在城市周围添加%通配符，并将其用作LIKE模式以与address匹配。

$cityCode=$db->query("
SELECT c.city AS bot 
FROM city_table AS c
JOIN people AS p ON p.address LIKE CONCAT('%', c.city, '%')
WHERE p.people_id = $zz");

Answer 3

$cityCode=$db->query("SELECT city AS bot,
FROM city_table WHERE city LIKE %(SELECT address FROM people WHERE 
people_id = $zz)%

我已经更新了解决方案。希望它能起作用。