joining multiple fields for soundex search

[Jocka]

Ok I have a table that holds the soundex codes for names. the table has name_id, soundex_id, soundex_code

 

the name id has the name id of course, soundex id holds any additional parts of the name (middle, last, whatever).

 

what I'm trying to do is find BOTH soundex codes if possible. Of course this doesn't work:

SELECT name_id FROM name_soundex WHERE soundex_code = SOUNDEX('name1') && soundex_code = SOUNDEX('name2')

Because these have 2 seperate soundex_id's. So what I need to do is maybe join anything with the same name_id  ???

 

Sorry I'm terrible with mysql. Plz help.

[Jocka]

just to get the idea if i didn't explain it right. the table is like this:

name_soundex
-----------------------------
name_id  -  soundex_id  -  soundex_code
1                 1                      N500
1                 2                      N500

so when I search I would join the name_id's that are similiar so that I can somehow just search as one row and be able to find "name1" and "name2" for a better result

[fenway]

I don't understand -- do you want it to match for BOTH name1 and name2, or EITHER name1 or name2?

[Jocka]

If there is more than one name submitted, then I want it to check against name1 AND name2 .. what I've decided to do for now is to just throw that extra soundex_code at the end of my "names" table.

I just add the code like S500 or N329 or whatever together "S500N329". Now I compare for an absolute match first, then follow that with a search on the soundex codes with this: "soundex_code LIKE '%$name1%' && soundex_code LIKE '%$name2%' . This way i can at least get similar outputs.

[fenway]

That still doesn't answer my question -- I know you're searching two names, but does it have to match BOTH?

[Jocka]

If there is more than one name submitted, then I want it to check against name1 AND name2

Yea, if more than one name is given, i want to compare against both names.

[fenway]

And fail if it doesn't match both?