SQL Query for Joining 2 tables where one of them has multiple values in one field

Question

Tables 1 as below

User_Option_Experience


user_id | teaching_in 
        |  
111     |   1,2,3
112     |   1,4
113     |   4,2

Tables 2 as below

Teaching_in


IdTeaching | name
           |  
1          |   Biology
2          |   Chemistry
3          |   Mathematics

Now what i want is to have an output of all the names of subjects a User (Teacher) teaches in because one user as seen from the table can teach many subjects

For example I want all the subjects for user with id 111

I tried this but no success as it returns only one record

select teaching_in.name 
  from teaching_in 
  left 
  join users_options_experience 
    on user_options_experience.teaching_in = teaching_in.IdTeachingin 
 where user_options_experience.user_id = 15605

Welcome to SO! Could you show us the query what you have tried so far? Please show us the expected output as well. — MBijen, Mar 19 '18 at 14:58
There's no need to show us what you've tried. Instead, focus your efforts on normalising your schema. — Strawberry, Mar 19 '18 at 15:01
This is a poor way of storing data. Look up how to structure a many-to-many relationship. — dfundako, Mar 19 '18 at 15:02
I cant change the database because this is how i found it when i started work here. — Marvin Dinaku, Mar 19 '18 at 15:03

score 0 · Answer 1 · answered Mar 19 '18 at 15:05

0

SELECT a.user_id, GROUP_CONCAT(b.name ORDER BY b.IdTeaching) DepartmentName FROM User_Option_Experience a INNER JOIN Teaching_in b ON FIND_IN_SET(b.IdTeaching , a.teaching_in) > 0 GROUP BY a.user_id

Answered by enter link description here

answered Mar 19 '18 at 15:05

user8286597

141
1
2

Thank you very much this is exactly what i was looking for – Marvin Dinaku Mar 19 '18 at 15:11

Sumesh TG · Answer 2 · 2018-03-19T15:10:52.657

0

SELECT  b.name 
FROM    User_Option_Experience a
        INNER JOIN Teaching_in b
            ON FIND_IN_SET(a.teaching_in , b.IdTeaching )
WHERE a.user_id=111;

Use FIND_IN_SET function of mysql

Hope this will help you.

edited Mar 19 '18 at 15:10

answered Mar 19 '18 at 15:05

Sumesh TG

2,367
1
12
29

SQL Query for Joining 2 tables where one of them has multiple values in one field

2 Answers2