SELECT * FROM table WHERE * LIKE (SELECT * FROM table WHERE * = *)-CodePudding

I have a separate table of Cities that have city codes in them and I also have a main table that contains a column 'address' which is only a short address. What I want is to select the city with code that is similar to the data in the 'address' column.

$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";

How to make this work?

CodePudding user response：

Use Exists in where clause like this:

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

CodePudding user response：

You need to add the % wildcard characters around the city, and use that as the LIKE pattern to match with 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");

CodePudding user response：

$cityCode=$db->query("SELECT city AS bot,

 FROM city_table WHERE city LIKE %(SELECT address FROM people WHERE 
 people_id = $zz)%

I have updated the solution. Hope it works.