CODEWITHSUNDEEP
mysqljoin
Chandara Sam
Chandara Sam

Reputation: 331

Get data with join in MySQL

I have table in MySQL as bellow:

Table item:

id item_name contact_id
1  Cocacola  1;3
2  Clothes   2;3

Table contact

id name     tel
1  Dona     xxx-xxx-xxxxx
2  Maro     xxx-zzz-zzzzz
3  Sana     yyy-mmm-mmmmm

Result need after join:

id item_name      contact_tel
1  Cocacola       xxx-xxx-xxxxx, yyy-mmm-mmmmm
2  Clothes        xxx-zzz-zzzzz, yyy-mmm-mmmmm

Can I get result like that using join in MySQL?

Upvotes: 0

Views: 112

Answers (1)

fthiella
fthiella

Reputation: 49049

SELECT
  item.id,
  item.item_name,
  GROUP_CONCAT(tel) contact_tel
FROM
  item LEFT JOIN contact
  ON FIND_IN_SET(contact.id, REPLACE(item.contact_id, ';', ','))
GROUP BY
  item.id,
  item.item_name

Please see fiddle here. Performance will be slow, since joining tables using FIND_IN_SET can't make use of an index.

Upvotes: 3

Related Questions