How to select column based on other table column?

Question

I have two tables: Table1 & Table2. Both the tables are given below:

here is table1

+----+----------+
| id | t1_title |
+----+----------+
| 1  | AAA      |
+----+----------+
| 2  | BBB      |
+----+----------+
| 3  | CCC      |
+----+----------+

here is table2

+----+----------+--------+
| id | t2_title | t1_IDs |
+----+----------+--------+
| 1  | John     | 1,3    |
+----+----------+--------+
| 2  | Sam      | 2      |
+----+----------+--------+
| 3  | Monty    | 1,2    |
+----+----------+--------+
| 4  | Rose     | 2,3    |
+----+----------+--------+

Now I want to get the table1.title (like AAA,BBB,CCC) based on the table2.t1_IDs , something like this:

+----+----------+-----------+
| id | t2_title | t1_titles |
+----+----------+-----------+
| 1  | John     | AAA,CCC   |
+----+----------+-----------+
| 2  | Sam      | BBB       |
+----+----------+-----------+
| 3  | Monty    | AAA,BBB   |
+----+----------+-----------+
| 4  | Rose     | BBB,CCC   |
+----+----------+-----------+

Fix your data model! Ids should not be stored in string lists. Numbers should not be stored as strings. Foreign key relationships should be properly declared! — Gordon Linoff, Jun 05 '19 at 18:15
First thing to do is not design a table that stores ids to another table as a comma delimited list. You need a pivot table to create a many to many relationship. — MikeS, Jun 05 '19 at 18:15

Ali Ghaini · Answer 1 · 2019-06-05T18:29:43.130

1

SELECT  a.id,
        t2_title,
        GROUP_CONCAT(b.t1_title ORDER BY b.id)
FROM    table2 a
        INNER JOIN table1 b
            ON FIND_IN_SET(b.t1_title, a.t1_IDs) > 0
GROUP   BY a.id,a.t2_title

db-fiddle

edited Jun 05 '19 at 18:29

answered Jun 05 '19 at 18:20

Ali Ghaini

882
6
13

How to select column based on other table column?

1 Answers1