0

I have a table with a column that contains as values the comma separated ids of a second table, how can I browse the first table showing instead the ids the values of the second table separated by commas?

ES: tab1: id, name, ids tab2: id2, name2

tab1: (1, 'mark', '1'); (2, 'john', '2,3'); tab2: (1, 'new york'); (2, 'rome'); (3, 'london');

ES query: SELECT tab1.id, tab1.name, (SELECT GROUP_CONCAT (name2) name2 FROM tab2 WHERE id2 IN (tab1.ids)) location FROM tab1 WHERE id = 2;

RESULT: '2', 'john', 'rome'

but if I manually put the values of the ids column in the condition, then it works

ES query: SELECT tab1.id, tab1.name, (SELECT GROUP_CONCAT (name2) name2 FROM tab2 WHERE id2 IN (2,3)) location FROM tab1 WHERE id = 2;

RESULT: '2', 'john', 'rome, london'

how come? thank you:)

Oggi è un altro post
  • 13
  • 3
  • `id2 IN (tab1.ids))` would be the same as `id2 IN ('2,3'))` - Note the quotes. And the string `'2,3'` is converted to the number `2` to compare with the `id2`. That's why you only get (2, 'rome') – Paul Spiegel Oct 26 '22 at 13:37

0 Answers0