0

I have 2 tables like this:

Table A:

Code Values
001 A:B:C
002 A:B:D

Table B:

Character Description
A Ai
B Bi
C Ci
D Di

I want to add a column Details in table A with descriptions from table B with the same format as Values column like this:

Code Values Details
001 A:B:C Ai:Bi:Ci
002 A:B:D Ai:Bi:Di

I tried to join 2 tables but got stuck how to display values in Details column. Could any one help an idea for a query?

Jack Lee
  • 19
  • 2
  • Check out the answers on this link. I think it will get you in the ballpark. [MySQL Join two tables with comma separated values](https://stackoverflow.com/questions/19101550/mysql-join-two-tables-with-comma-separated-values). The key is using `find_in_set()` function. Also worth noting that this is likely to be a pretty slow query with large data, but that slowness is due to the not-amazing way of storing the data in a delimited string instead of its own table. – JNevill Dec 17 '21 at 16:00
  • @JNevill: Thanks for directing. Does find_in_set() apply for a list (separated by comma) only? my data is currently using ":" character like above example, is it possible to apply? – Jack Lee Dec 17 '21 at 16:12
  • [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462) Or woudl it be easier to design the database properly – RiggsFolly Dec 17 '21 at 16:16
  • That's a fine point. I've never used the function before, but it looks like it's limited to comma delimited lists. Perhaps wrap that column reference with a `Replace(Values, ':',',')` and see if that does the trick. – JNevill Dec 17 '21 at 16:16
  • 1
    As you can see, this gets more and more complicated. The complications would go away if you normalized the data. – Barmar Dec 17 '21 at 16:59
  • To add to Barmar's point too... this type of complication is not only paid for in difficult to write and manage sql, but also in speed to query and system resource consumption. Cost-wise it's likely cheaper to just fix the data unless this is a one-time thing where speed isn't terribly important. – JNevill Dec 17 '21 at 18:52

0 Answers0