Matching two columns in MySQL

Question

I'm quite new to SQL and have a question about matching names from two columns located within 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 row is returned if the two names within that row match. Now I'd also like to get those name matches between column1 and column2 that aren't in the same row. Is there a way to automate a procedure whereby every name from column1 is compared to every name from column2?

Thanks :)

p.s.: If anyone could point me in the direction of a n-gram/bi-gram matching algorithm that is easy for a noob to implement into mysql that would be good as well.

Answer 1

If your table has a key, say id, you 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 itself on that relationship as such:

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