Get data from 2 tables based on comma separated values Join

Question

I am an average in database operation but I never deal with a database having values in comma separated manner. As I want the result from both table.

I have one table "Form table"

+------+-----------------+
| id   | Fname           |
+------+-----------------+
| 1    | BH01101,BH01102 |
| 2    | f_934_tarun2    |
| 3    | f_934,T_3234    |
+------+-----------------+

2nd table "user assigned form table"

+------+--------------------------------+
| u_id | Fname                          |
+------+--------------------------------+
| 1    | BH01102,BH16101,BH16102,BH16   |
| 2    | f_934_tar,f_934_fhh            |
| 3    | f_934_ttt,T_3234               |
| 4    | f_934_a1,BH01102               |
+------+--------------------------------+

I want to get all user ids who has any of form listed in form table. So here the output will be:

+------+
| u_id |
+------+
| 1    |
| 3    |
| 4    |
+------+

Every help will be appreciated.

Answer 1

I am an average in database operation ...

Clearly this table was designed by someone of below average ability.

... I never deal with a database having values in comma separated manner.

Nor should you. This "design" is just plain wrong.

Your tables should look more like this:

select * from forms ; 

+------+--------------+
| id   | Fname        |
+------+--------------+
| 1    | BH01101      |
| 1    | BH01102      |
| 2    | f_934_tarun2 |
| 3    | f_934        |
| 3    | T_3234       |
+------+--------------+

select * from user_forms ; 

+------+-----------+
| u_id | Fname     |
+------+-----------+
| 1    | BH01102   |
| 1    | BH16101   |
| 1    | BH16102   |
| 1    | BH16      |
| 2    | f_934_tar |
| 2    | f_934_fhh |
| 3    | f_934_ttt |
| 3    | T_3234    |
| 4    | f_934_a1  |
| 4    | BH01102   |
+------+-----------+

Then your query becomes

select uf.id id 
from forms f 
inner join user_forms uf 
      on f.if = uf.u_id 
     and f.fname = uf.fname
group by uf.id 
order uf.id ; 

+----+
| id | 
+----+
|  1 | 
|  3 | 
|  4 | 
+----+

Go back to whoever dumped you with this rubbish and get them to sort it out.