0

Mariadb version 10.3.34. SQL to create the example tables is on this gist.

I have to work with a foreign database on which I have no control. So suggestions to modify the structure of the DB are, sadly, unacceptable. I can add functions, though.

Now, in this database, things can have from 0 to n colors, and the color references are coded as a string of all values joined by a | char, like this :

+----------------------+
|          things      |
| name (pkey)| colorsid|
+------------+---------+
| 'door'     | '20|5'  |
| 'car'      | '10'    |
| 'hammer'   | null    |
| 'box'      | '5'     |
+------------+---------+

+------------------+
|    colors        |
| id   | color     |
+------+–––––––––––+
| 5    | 'red'     |
| 10   | 'blue'    |
| 20   | 'black'   |
+------+–––––––––––+

So the door is black and red, the car is blue, the hammer has no color, and the box is red.

How to “JOIN” on each color code for each thing ?

I'd like a query whose result is this :

+---------------------------+
|       thingsandcolors     |
| name       | colornames   |
+------------+--------------+
| 'door'     | 'black|red'  |
| 'car'      | 'blue'       |
| 'hammer'   | null         |
| 'box'      | 'red'        |
+------------+--------------+

Performance is not an issue (to a reasonable extent, of course). The DB is expected to contain at most a few tens of colors, and no more than 10 000 things.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
exore
  • 194
  • 11
  • don't save delimited data in columns see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – nbk Oct 16 '22 at 09:26
  • can't you read ? I have no choice. That is how the db is. – exore Oct 16 '22 at 09:28
  • that is why i posted the link, you nee to split the text (there a tons of functions) and then join the tables. also you have two questions, allowed is only one – nbk Oct 16 '22 at 09:32
  • OK. removed the second question and will post a new question. I know what I have to do, split the string and join. I've already spent time trying to do that. But I can't. So I ask. Your link does not give the slightest clue as how to do it. All there is is "this is bad", "this is not in normal form", "this has drawbacks" and so on. All true, all useless – exore Oct 16 '22 at 09:40
  • like i said first split the delimted column into rows https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows second join the second table third GROUP_COMCAT the result of the join, that is in a nutshell all you need to do – nbk Oct 16 '22 at 17:18
  • thanks @nbk, I've started to try things based on this answer. json_table is very interesting. I have to go to work now, but will most probably continue this evening. – exore Oct 17 '22 at 04:43

0 Answers0