MySQL query to linked 2 tables with PDO-CodePudding

I've these 2 tables in MySQL:

Table ___Photos:

|--------|-----------|-------------|
| AMP_Id | AMP_Photo | AMP_Formats |
|--------|-----------|-------------|
|      1 |   dog.jpg |         1,2 |
|      1 |   cat.jpg |           3 |
|--------|-----------|-------------|

Table ___Formats:

|--------|------------|
| AMF_Id | AMF_Format |
|--------|------------|
|      1 |      10x10 |
|      2 |      30x30 |
|      3 |      40x40 |
|--------|------------|

How can I list each Photos with the formats linked in ___Formats ?

For example, for the tables show above, I would need something like this:

|-----------|-------------|
| AMP_Photo |  AMF_Format |
|-----------|-------------|
|   dog.jpg | 10x10,30x30 |
|   cat.jpg |       40x40 |
|-----------|-------------|

This is what I have tried so far:

SELECT
    AMP_Photo,
    IFNULL(GROUP_CONCAT(AMF_Format), "") as list_formats
FROM ___Photos i
LEFT JOIN ___Formats f
    ON f.AMF_Id = AMF_Format
GROUP BY AMP_Photo
ORDER BY AMP_Photo ASC

CodePudding user response：

You can do it as follows using concat :

SELECT
    AMP_Photo,
    IFNULL(GROUP_CONCAT(AMF_Format), "") as list_formats
FROM ___Photos i
INNER JOIN ___Formats f ON i.AMP_Formats like concat('%',f.AMF_Id,'%')
GROUP BY AMP_Photo
ORDER BY AMP_Photo ASC

demo here : https://dbfiddle.uk/eiQIgI8f