0

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.

3
  • 1
    Which RDBMS and version are you using? CREATE TABLE/INSERT would be helpful Sep 14, 2021 at 10:35
  • @Charlieface MySQL 5.6 Sep 22, 2021 at 12:01
  • @ErgestBasha Its in production so there is no option to redesign as far now. I agree. but it was previously designed Sep 22, 2021 at 12:03

1 Answer 1

-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.

1
  • No option to redesign as far now. and if you want to give solution, it would be great, but using a aggressive language will not help anybody. Sep 22, 2021 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.