Join a table for every match of values in MYSQL-CodePudding

I have two tables and I wanna join them. There is one value artist_IDs, which stores all artist IDs for that album. I wanna to get all artists data of that album but I can't get it to work, because Locate doesn't work and neither is Like. Do u have any idea how I can achieve this in MySQL with MariaDB?

SELECT artists.name,artists.ID from artists WHERE LOCATE(artists.ID,"2Im8m4STDBosjfmb5hmP80,1pkDzgHvVqC17qYGKfLPpb")

This query like how I want it to have. It returns two artists because there are two IDs given. But it doesnt work in my needed statement:

SELECT albums.ID, albums.name, albums.imageID,artists.name as artistsname FROM albums INNER JOIN (SELECT artists.name,artists.ID from artists) artists ON LOCATE(artists.ID,albums.artist_IDs) GROUP BY albums.ID

Thats what I get but I want, for the album where name= No Richt Parents two entries. One with the artistsname Mozzik and the other one with the artistsname Loredana because their IDs are stored in the albums.artists_IDs field.

Here u can download the two tables: Link

CodePudding user response：

You do not use aggregate function, hence only one (indefinite) value for a group is returned. You must aggregate your data.

-- your second query, fixed version
SELECT albums.ID, albums.name, albums.imageID, GROUP_CONCAT(artists.name) as artistsname 
FROM albums 
INNER JOIN ( SELECT artists.name, artists.ID 
             FROM artists) artists ON LOCATE(artists.ID, albums.artist_IDs) 
GROUP BY albums.ID

-- more clear code
SELECT albums.ID, albums.name, albums.imageID, GROUP_CONCAT(artists.name) AS artistsname 
FROM albums 
INNER JOIN artists ON LOCATE(artists.ID, albums.artist_IDs) 
GROUP BY albums.ID, albums.name, albums.imageID

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=aa20427202459a732f4c5e86315a32f2

PS. Do not disable ONLY_FULL_GROUP_BY SQL server mode.