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

Question

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?

Does this answer your question? [Using the SQL LIKE operator with %%](https://stackoverflow.com/questions/1476737/using-the-sql-like-operator-with) — emrdev, Mar 17 '22 at 08:20
What RDBMS are you using? MySQL, SQL-Server, Oracle, etc.? This affects the syntax for concatenating strings. — Barmar, Mar 17 '22 at 08:25

Barmar · Answer 1 · 2022-03-17T08:22:15.540

0

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

edited Mar 17 '22 at 08:22

answered Mar 17 '22 at 08:15

Barmar

741,623
53
500
612

``SELECT city AS bot `` could be ambiguous as we don't know table structure, ``SELECT c.city AS bot`` is better – Alaindeseine Mar 17 '22 at 08:21
1

True, although if there were a `city` column in `people` we'd probably be joining on it instead of doing a pattern match on `address`. – Barmar Mar 17 '22 at 08:23
I agree, but since we don't know the structures, it's best to plan ahead – Alaindeseine Mar 17 '22 at 08:26

score 0 · Answer 2 · answered Mar 17 '22 at 08:21

0

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

answered Mar 17 '22 at 08:21

sa-es-ir

3,722
2
13
31

Ahmed Memon · Answer 3 · 2022-03-17T08:26:56.390

-1

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

edited Mar 17 '22 at 08:26

answered Mar 17 '22 at 08:22

Ahmed Memon

120
5

You can't refer to a column alias in the `WHERE` clause of the same query. And you have to use `CONCAT()` to concatenate the `%` characters. – Barmar Mar 17 '22 at 08:24
You have `AS bot` in the wrong place. – Barmar Mar 17 '22 at 08:24

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

3 Answers3