I have a table with adresses, which were separated by spaces into different columns. Since the name of the streets can be made up from 1-2-3 etc. parts, the street suffices are placed in different columns. I have a list of the possible street suffices and I want to check the columns and select the records which are matching to any of the street suffices.
For example if I have a table like:
C1 C2 C3 C4 ------------------------ Abbey road Made up street Some Other Weird lane Time square
And also I have the table containing the “list” of possible suffices:
C5 -------- avenue road street way lane square drive boulevard etc...
What I would like to have is something like this:
C5 -------- road street lane square
Is there a possible way to do this? I was looking for similar questions, but couldn’t find any.
Advertisement
Answer
You could join two tables using IN as condition:
SELECT * FROM table_1 t1 JOIN table_2 t2 ON t2.C5 IN (t1.c1, t1.c2, t1.c3, t1.c4)