0

I Have a table with a table with a field containing ids and want make a LEFT JOIN ON id IN

table a

|id|invoice|contents|
|1 |123    |1,3     |

table b

|id|content|
|1 |aaa    |
|2 |bbb    |
|3 |ccc    |

SELECT invoice, content
FROM a
LEFT JOIN b ON b.id IN (a.contents)

The result is only 1 row with the first "content.id" an not two rows. mysql converts the string to a number, right? How to solve this?

Jens
  • 67,715
  • 15
  • 98
  • 113
Hannes
  • 1
  • A `like` statement might help, you could do something such as; `SELECT invoice, content FROM a RIGHT INNER JOIN b ON a.contents LIKE CONCAT( '%', a.id, '%' )`. I do NOT reccomend this (see comment below from Jens as to why) but this [question](https://stackoverflow.com/questions/1930809/mysql-join-query-using-like) should help with the like part – Can O' Spam Jul 19 '22 at 11:11
  • 4
    Do not store foreign keys as csv. Read about normalization – Jens Jul 19 '22 at 11:11
  • 1
    `.. JOIN .. ON FIND_IN_SET(content, contents) ..` – Akina Jul 19 '22 at 11:17

0 Answers0