Mysql – How to match string in varchar field from one table with other table

MySQL

I have two tables,

table1 having a single column:

id     |   sentence(varchar 255)
1      |   I am a boy; a good boy.

and table2 having two columns:

word    |   meaning
I       |     مين
am      |     هون
a       |     أيك 
boy     |     لرقة 
good    |     اشا

Now I want to query both tables in such a way that I will get each word with its translation.

For the sample data the query result must be:

sentence                  |    word_meaning (virtual column)
I am a boy; a good boy.   |  {"I":"مين", "am":"هون", "a":"أيك", "boy":"لرقة", "good":"اشا"}

As you can see in word_meaning column, each word has its meaning with json.

Best Answer

What client language are you using? That is where you need to split a sentence into "words" and where you will need to put the output back together after fetching the individual meanings. Do not try to do it in SQL.

Related Solutions

MySQL query to search string in Master-Child tables and return all rows with Master-Child relation

SELECT  t1.*, 
        t2.*

FROM    Table1 AS t1
    LEFT JOIN
        Table2 AS t2
            ON WHERE t2s.FK_id = t1.PK_id

WHERE   t1.Table1_Title = 'search text'
   OR   EXISTS
        ( SELECT  *
          FROM    Table2 AS t2s
          WHERE   t2s.FK_id = t1.PK_id
            AND   t2s.Table2_Title = 'search text'
        ) ;

MySQL: Join one Row in a table with two Rows in other table

Perhaps I'm misunderstanding your request, but based on the text in your question that sounds simple enough:

SELECT
    U.first_name AS "User Name",
    U.email_id AS "User E-Mail Address",
    BossUser.first_name AS "Authorizing User Name"
    BossUser.email_id AS "Authorizing User E-Mail Address"
FROM
    user_login AS UL
    INNER JOIN users AS U ON UL.user_id = U.user_id
    LEFT JOIN  user_login AS BossLogin ON U.created_by = BossLogin.user_name
    LEFT JOIN  users AS BossUser ON BossLogin.user_id = BossUser.user_id

This allows for users without a created_by; if that field is NOT NULL, you can replace the LEFT JOINs with INNER JOIN.

Best Answer

Related Solutions

MySQL query to search string in Master-Child tables and return all rows with Master-Child relation

MySQL: Join one Row in a table with two Rows in other table

Related Question