0

I am performing a soundex query on a table of users. A subquery of users is provided as a comma separated list of strings.

I want to do something akin to the following, but I cannot find the write syntax to make this work.

select * from ((Select soundex(concat(fname, lname)) t, * from users)
Union
(Select soundex(fname) t, * from users)
Union
(Select soundex(lname) t, * from users)) xusers
where t in (select soundex([column]) from ('Name 1', 'Name 2', 'Name 3', 'Name N-1', 'Name N'))

Later I plan to optimize this query by having a table with the soundex values mapped to column id's, however, I'm not sure now I can reference an anonymous column when my subquery is a comma delimited list? How do I add an alias, or how can convert this list to a list of soundex values to perform comparisons with?

Marc B
  • 356,200
  • 43
  • 426
  • 500
The Prophet
  • 338
  • 5
  • 11
  • 1
    the entire `select sound([column])` sub-select is outright syntax error. you cannot magically "create" a table like that in mysql. – Marc B Jul 16 '13 at 14:39
  • I know mark, I do not know how to reference the only column that exists in the 'table' in the from clause of the subquery. if i did, then i would do soundex(columnalias) – The Prophet Jul 16 '13 at 14:52

1 Answers1

0

I was able to accomplish this by utilizing a temporary table inserting the list into my temporary table and then joining the temporary table with my query table to get the desired result.

create temporary table searchvalues (name char(250));
insert into searchvalues values ('Name 1'),('Name 2');
select distinct id from users inner join searchvalues on soundex(fname) = soundex(name) or     soundex(lname) =soundex(name) or soundex(concat(fname,lname)) = soundex(name);
The Prophet
  • 338
  • 5
  • 11