'SELECT * FROM table WHERE * LIKE (SELECT * FROM table WHERE * = *)

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

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'

How to make this work?

phpsqlselectsql-like


Solution 1:[1]

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+'%' )

Solution 2:[2]

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

Solution 3:[3]

$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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Saeed Esmaeelinejad
Solution 2
Solution 3

Related Questions