SQL字符串替换为ID语句

Question

Is it possible to create a sql statement to do the following:

Table 1 lists all of the drug names, Table 2 contains all of the side effects.

Table 3 contains all the drug names by ID and all of the side effects separated by |.

Is there some kind of SQL query I can run to re-create Table 3 where the side effects separated by | are the side effect ID's from Table 2?

Table1
---------------------
id | drug_name
---------------------
1  | aspirin
2  | zoloft
3  | codine


Table2
---------------------
id | side_effects
---------------------
1  | rash
2  | hearing loss
3  | the plague


Table3
---------------------
id | drugs2sidefx
---------------------
1  | rash | hearing loss
2  | 
3  | the plague | hearing loss

Answer 1

You don't need table3 this way.

it should be

drugs2se
---------------------
d_id | se_id
---------------------
1    |  1
1    |  2
3    |  2
3    |  3

Then you can get desired results with a query like this

SELECT d.name, group_concat(se.name) as effects 
FROM drugs d, drugs2se dse, side_effects se 
WHERE d.id=d_id AND se_id=se.id 
GROUP BY (d.id)