MySQL join multi record by one field

Question

I am stucking with this case, it's about join two tables and return multi record.

Let's assume that i have two tables like this:

Table product:

+----+-----------+-----------+
| id |    name   | media_id  |
+----+-----------+-----------+
| 1  + product 1 +  32,33,34 +
+----+-----------+-----------+
media_id( VARCHAR(50) )

Table media:

+----+-----------+------------------------------------------+
| id |    name   |                  path                    +
+----+-----------+------------------------------------------+
| 31 + media 1   +     localhost://uploads/image/image_1    +
+----+-----------+------------------------------------------+
| 32 + media 2   +     localhost://uploads/image/image_2    +
+----+-----------+------------------------------------------+
| 33 + media 3   +     localhost://uploads/image/image_3    +
+----+-----------+------------------------------------------+
| 34 + media 4   +     localhost://uploads/image/image_4    +
+----+-----------+------------------------------------------+

I tried to use JOIN with WHERE IN clause:

SELECT 
m.id,
m.name as media_name,
p.name,
p.media_id

FROM 
media as m
JOIN product as p

on m.id in (p.media_id);

and other JOIN clause but the return result is:

+----+-----------+-----------+------------+-------------------------+
| id |    name   | image_id  | media_name +          path           + 
+----+-----------+-----------+------------+-------------------------+
| 1  + product 1 +  32,33,34 +  media 2   +   uploads/image/image_1 +
+----+-----------+-----------+------------+-------------------------+

What i want is the return result have 3 records like this:

+----+-----------+-----------+------------+-----------------------+
| id |    name   | image_id  | media_name +          path         +
+----+-----------+-----------+------------+-----------------------+
| 1  + product 1 +     32    +  media 2   + uploads/image/image_2 +
+----+-----------+-----------+------------+-----------------------+
| 2  + product 1 +     33    +  media 3   + uploads/image/image_3 +
+----+-----------+-----------+------------+-----------------------+
| 3  + product 1 +     34    +  media 4   + uploads/image/image_4 +
+----+-----------+-----------+------------+-----------------------+

Does anyone know how to join table like this?

@Strawberry i understand the normal schema of image_id is `int`, and i can make more columns like `image_id_2`, `image_id_3`, `image_id_4`. But in my case, i need to change the `image_id` column into `VARCHAR` — namdh, Oct 20 '17 at 09:33
It's plain that you don't understand. But don't despair. The basic principles of normalisation are actually pretty easy to grasp (I managed to understand it, after all!). — Strawberry, Oct 20 '17 at 09:36

score 1 · Answer 1 · answered Oct 20 '17 at 11:05

In order to achieve what you are looking for would require a slight change to your DB schema, removing the comma separated values from the product table.

You could then use a link table which you could then query for the join.

Products

| id | name      |
|----|-----------|
| 1  | product 1 |
| 2  | product 2 |
| 3  | product 3 |

product_media Links

| id | product_id | media_id |
|----|------------|----------|
| 1  | 1          | 1        |
| 2  | 1          | 2        |
| 3  | 1          | 3        |

You can then do a query that checks the links table and returns the multiple row result you are looking for.

e.g.

SELECT 
m.id,
m.name as media_name,
p.name as product_name
m.path

FROM 
media as m
LEFT JOIN product_media_links as pl on m.id = pl.media_id
LEFT JOIN products as p on p.id = pl.product_id

using left join to return media with no products if you so wish

Also as mentioned by someone else, take a look at normalization. It really does help when designing your databases, and you can choose how far you want to go with it.

Yes, I understand the case modify DB Schema. I use this kind of DB for my website, function Upload multi image and i think use field media_id as VARCHAR is better than INT, i mean group media into "media_id, media_id, media_id" is better than separate it to 3 records — namdh, Oct 23 '17 at 06:21
You can use a comma-separated list, yes, but you will also find that indexing on ID's and using a link table gives you faster results, also your join will not work with a comma-separated list as a string, which was the original problem with your code above. https://stackoverflow.com/questions/2346920/sql-select-speed-int-vs-varchar — WillHazell, Nov 10 '17 at 15:13

MySQL join multi record by one field

1 Answers1