Question 22184 in Mysql Question posted in
The official MySQL documentation can be found here.

filter for row containing words (string) from a different table SQL – Mysql

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

Tags:

2

Answers


  1. 0

    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, '%');
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search