filter for row containing words (string) from a different table SQL-CodePudding

So I am trying to select all the rows that contains string equal to values from another table.

SELECT q.QUERY, f.ANONID 
FROM FACTS f
INNER JOIN QUERYDIM q 
ON f.QUERYID = q.ID 
WHERE q.QUERY IN (SELECT city FROM zipcodes);

q.QUERY is a string with different lengths I want to select all the q.QUERY that contains value that is equal to any value in city

example

one row:
q.QUERY = "this is a city called berlin"

another row:

q.QUERY = "in cairo it is nice" 


SELECT city FROM zipcodes = ("berlin","Birmingham","Huntsville",etc..)

so each q.QUERY loops through all the cities in the table every time

I am trying to find all the rows that has values of city

CodePudding user response：

Something like this, assuming you don't want duplicate rows if the query column contains several cities:

select q.query, f.anonid 
from facts f
inner join querydim q on f.queryid = q.id 
where exists (select 1 from zipcodes where locate(city, q.query));

The locate function searches for the first string in the second string, returning the index if found, zero otherwise, and zero converts to false.

CodePudding user response：

You could use LIKE operator and a full join on zipcodes

SELECT q.QUERY, f.ANONID 
FROM FACTS f
INNER JOIN QUERYDIM q ON f.QUERYID = q.ID 
INNER JOIN  ( SELECT city FROM zipcodes ) t on q.query like concat('%',t.city, '%');