Search code examples
mysqlrdbmsdatabase

Mysql search query with join and subqueries

I have a large Mysql database connected with a php platform.

I have few table with this structure:

Table USER:

id (int auto increment not null, primary key)
name (varchar 200)
spoken_lang (varchar 400) /* The value of this field is the id of the table languages, 
                             with the char ';' like a separator ;*/

Table LANGUAGES

id (int auto increment not null, primary key) /* Used also in the prev table */
name (varchar 200)

Is it possible to make a single search query that is be able to compare the column user.name, and also the table languages.name?

Something like this:

SELECT user.name, languages.name as lang 
FROM 
user JOIN languages ON user.spoken_lang = ......
Solution

  • I would first have to recommend you normalize your table -- you shouldn't be storing a list of results in a single column. If possible, consider creating a User_Language table which stores the User_Id and the Language_Id.

    However, you can achieve the same results using FIND_IN_SET and REPLACE:

    select u.name, l.name language
from user u
  join languages l on find_in_set(l.id, replace(u.spoken_lang,';',','))