0

Table1 :
id1 | ref_table2 (tinytext)
1    | 2,3,5
2    | 1,3,4
3    | 2

Table2 :
id2 | name2 (tinytext)
1     | abc
2     | def
3     | ghi
4     | jkl
5     | mno
6     | pqr

If I take a specific entry from table1, I'd like to list all table2 items whose ids belong to the "ref" string in table1 (2,3,5).

The desired output for "2,3,5" would be :
id | name
2  | def
3  | ghi
5  | mno

user3833734
  • 1
  • 3

1 Answers1

1

It is bad practice to store multiple values in one column. I'd recommend using an M:N table instead. The structures would look like this

M:N table-
table1_val | ref_table2
  1        |   2
  1        |   3
  1        |   5
  2        |   1
  2        |   3
  2        |   4
  3        |   2

Then you can run a simple query

SELECT ref_table2 FROM mn_table WHERE table1_val = 1

will return 2,3, and 5. So you can use a nested query, which I believe would look as follows:

SELECT * FROM table2 WHERE id2 in (SELECT ref_table2 FROM mn_table WHERE table1_val = 1)
Yonah Karp
  • 501
  • 5
  • 21