Search code examples
mysqlleft-joingroup-concatfind-in-set

Join three tables with comma separated values in mysql

I have 3 tables as below

**tbl_link**
╔═════════════╦═══════════════╦═══════════════╗
║ subject_id  ║ system_csv_id ║class_csv_id   ║
╠═════════════╬═══════════════╬═══════════════╣
║ 1           ║ 4,3           ║ 5,1,3,4       ║
║ 2           ║ 1,3           ║ 2,3           ║
╚═════════════╩═══════════════╩═══════════════╝

**tbl_system**                           **tbl_class**
╔═══════════╦════════════════════╗       ╔═══════════╦════════════════════╗
║ system_id ║ system_desc        ║       ║ class_id  ║ class_desc         ║                
╠═══════════╬════════════════════╣       ╠═══════════╬════════════════════╣
║ 1         ║ ANY                ║       ║ 1         ║ GRADE 8            ║
║ 2         ║ LOCAL SYSTEM       ║       ║ 2         ║ GRADE 9            ║
║ 3         ║ BRITISH SYSTEM     ║       ║ 3         ║ GRADE 10           ║ 
║ 4         ║ AMERICAN SYSTEM    ║       ║ 4         ║ GRADE 11           ║
╚═══════════╩════════════════════╝       ║ 5         ║ GRADE 12           ║
                                         ╚═══════════╩════════════════════╝

I want to query tbl_link and associate the 'system_csv_id' -> 'system_desc' and 'class_csv_id, -> 'class_desc' to reach following output.

╔═════════════╦═══════════════════╦═════════════════════════════════════════╗
║ subject_id  ║ system            ║ class                                   ║
╠═════════════╬═══════════════════╬═════════════════════════════════════════╣
║ 1           ║ AMERICAN,BRITISH  ║ GRADE 12, GRADE 8, GRADE 10, GRADE 11   ║
║ 2           ║ ANY, BRITISH      ║ GRADE 9, GRADE 10                       ║
╚═════════════╩═══════════════════╩═════════════════════════════════════════╝

I did some searches to find solution. All i got that this can be achieved using (CONCAT or GROUP_CONCAT) and ON FIND_IN_SET() but as my SQL knowledge is very limited, I am unable to achieve the required output.

Any help shall be highly appreciated.

Solution

  • Join the tables and use group_concat():

    select l.subject_id,
  group_concat(distinct s.system_desc order by find_in_set(s.system_id, l.system_csv_id)) system,
  group_concat(distinct c.class_desc order by find_in_set(c.class_id, l.class_csv_id)) class
from tbl_link l
inner join tbl_system s on find_in_set(s.system_id, l.system_csv_id) 
inner join tbl_class c on find_in_set(c.class_id, l.class_csv_id) 
group by l.subject_id

    See the demo.
    Results:

    | subject_id | system                         | class                              |
| ---------- | ------------------------------ | ---------------------------------- |
| 1          | AMERICAN SYSTEM,BRITISH SYSTEM | GRADE 12,GRADE 8,GRADE 10,GRADE 11 |
| 2          | ANY,BRITISH SYSTEM             | GRADE 9,GRADE 10                   |