CODEWITHSUNDEEP
mysqlsqldatabasecsvselect
Rob van den Eijnden
Rob van den Eijnden

Reputation: 23

Translate words from one database with other database translation

I have two databases, one database containing all my pictures like:

Database 1 name: images
-----+----------------------------+----------------------+-----------+------------+------------
| id | description                | tags                 | imagename | resolution | location  |
-----+----------------------------+----------------------+-----------+------------+------------
| 1  | We standing at eiffeltower | france, green, blue  | IMG01.JPG | 1280x1024  | /img/2020 |
| 2  | We standing at bridge      | france, orange, grey | IMG02.JPG | 1280x1024  | /img/2020 |

Database 2 name tagTranslations (for Dutch translation)
-----+--------+-----------------------
| id | tag    | translation          |
-----+--------+-----------------------
| 1  | france | frankrijk            |
| 2  | orange | oranje.              |
| 3  | grey   | grijs.               |
| 4  | green  | groen                |
| 5  | blue   | blauw                |

Now i want with 1 mysql query to get a result like this:
"We standing at eiffeltower", "france, green, blue", "IMG01.JPG", "1280x1024", "/img/2020", "frankrijk", "groen", "blauw"

"We standing at bridge", "france, orange, grey", "IMG02.JPG", "1280x1024", "/img/2020", "frankrijk", "oranje", "grijs"

Upvotes: 1

Views: 85

Answers (2)

Maria Israel Sathyan
Maria Israel Sathyan

Reputation: 1

Try out the code below:

CREATE VIEW table_comb AS
   SELECT * FROM images
   UNION ALL
   SELECT * FROM tagTranslations

Upvotes: 0

GMB
GMB

Reputation: 222632

You first effort should go into fixing your data modeL Each image tag should be stored in a separate table, on a different row. Storing delimited lists in database columns in the root of many evils, as you are starting to see. More about this can be read in this famous SO post.

That said, you could use a corelated subquery with find_in_set() and group_concat():

 select
    i.id,
    i.description,
    (
        select group_concat(
            tt.translation 
            order by find_in_set(tt.tag, replace(i.tags, ', ', ','))
            separator ', ' 
        )
        from tagTranslations tt
        where find_in_set(tt.tag, replace(i.tags, ', ', ','))
    ) tags,
    i.imagename,
    i.resolution,
    i.location
from images i

The correlated subquery retrieves rows from the translation table whose tag can be found in the tags list of the corresponding images row. For this, we use handly MySQL function find_in_set() (we need to remove the space after the comma for the function to work properly); then, aggregation function group_concat() regenerates a delimited list of translations, using find_in_set() again to honor the original order of tags.

Demo on DB Fiddle:

id | description                | tags                     | imagename | resolution | location 
-: | :------------------------- | :----------------------- | :-------- | :--------- | :--------
 1 | We standing at eiffeltower | frankrijk, groen, blauw  | IMG01.JPG | 1280x1024  | /img/2020
 2 | We standing at bridge      | frankrijk, oranje, grijs | IMG02.JPG | 1280x1024  | /img/2020

Upvotes: 1

Related Questions