SQL questions 3 replies

Please wait...

MrFancypants Forum Administrator

The Bad

218,788 XP

7th December 2003

0 Uploads

20,063 Posts

12 Threads

#1 11 years ago

Hopefully we have a few people who know a bit about mySQL here. I'm trying to get a few things to work and need a bit of help.

My first question would be about matching names. I have a table with two columns filled with names. Now I want to match these names. I already have a few matching algorithms, lets take the preinstalled soundex() as example here. Soundex turns strings that sound similar into identical number combinations, so the query

SELECT * FROM tablename WHERE SOUNDEX(namecolumn1)=SOUNDEX(namecolumn2);

compares the names and returns matches. But it only compares the entries line by line, so if two names are similar that are right next to each other that works fine, but if there are two similar (or identical) names in different lines they won't show up. Now how do I have to change the query so that each entry of column2 is compared to each entry in column1?

The name lists are quite long, so comparing each name from one list to the other column manaully won't work. Maybe something could be done by generating two more columns with the soundex() results?

edit: nevermind, I figured it out (well, someone else figured it out for me, actually) :)




>Omen<

Modern Warfare

50 XP

1st January 2005

0 Uploads

7,395 Posts

0 Threads

#2 11 years ago

I looked into mySQL once for something I don't now recall, and found the language structure to be hard to get too. I did however find a beginning tutorial that seems to address Structured Query Language though. Hope it helps. Beginning MySQL Tutorial

There's also of course this, if you haven't asked here: http://forums.mysql.com/list.php?10




Rookie Advanced Member

128,030 XP

3rd May 2005

0 Uploads

11,953 Posts

0 Threads

#3 11 years ago

I'm guessing you solved this using a self-join of some kind?




MrFancypants Forum Administrator

The Bad

218,788 XP

7th December 2003

0 Uploads

20,063 Posts

12 Threads

#4 11 years ago
Rookie;5236834I'm guessing you solved this using a self-join of some kind?

Yep, there are apparently several methods to join a table with itself. I can imagine how joining two different tables on common elements works, but joining a table with itself seems a little weird.