Matching two columns in MySQL

Question

I am new to SQL and ask a question about matching names from two columns located inside a table:

Let's say I want to use the soundex () function to match two columsn. If I use this query:

SELECT * FROM tablename WHERE SOUNDEX(column1)=SOUNDEX(column2);

a string is returned if two names inside this string match. Now I would also like to get these name matches between columns 1 and column2 that are not on the same row. Is there a way to automate the procedure in which each name from column 1 is compared with each name from column 2?

Thank:)

ps: If someone could point me towards the n-gram / bi-gram matching algorithm, which is easy for noob to implement in mysql, that would be nice too.

Answer 1

If your table has a key, let's say idyou can try:

select A.column1, B.column2 
from tablename as A, tablename as B 
where (A.id != B.id) and (SOUNDEX(A.column1) = SOUNDEX(B.column2))

Answer 2

You can join the table to yourself for these relationships as such:

SELECT * FROM tablename t1 JOIN tablename t2 
ON SOUNDEX(t1.column1) = SOUNDEX(t2.column2);