Click here to Skip to main content
15,667,864 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,
Table 1
1		1
2		5

Table 2

Form Name	ROLE
1		1	
2		1
3		5
4		1,5

I need a query to fetch form names where UID=2 which will display like below

Form Name

Then the O/P will be
Form Name 

What I have tried:

Table 1 INNER JOIN Table 2 ON  T2.Role IN ('5','1')

Updated 3-May-21 21:51pm

1 solution

Basically, don't store it like that: comma delimited data is a PITA to work with in SQL because it;'s string handling is ... um ... poor.

And because you want to find "1" or "5" in both "1" and "1, 5" you really don't want a CSV column.
Instead, have each role on a separate row:
FormName    ROLE
1              1	
2              1
3              5
4              1
4              5
And then use a JOIN to combine them. It all becomes a lot simpler to work with:
SELECT DISTINCT t2.FormName FROM Table2 t2
JOIN Table1 t1 ON t1.Role = t2.ROLE
WHERE t1.UID = 2
Share this answer
Member 11658469 4-May-21 3:59am    
Is there any way to check an sql command to fetch roles with 1,5
CHill60 4-May-21 4:16am    
If you are going to ignore @OriginalGriff's sound advice then use PATINDEX. But don't complain here when you fail your course.
OriginalGriff 4-May-21 4:18am    
It's really not a good idea - when you use CSV data in a column, you really make everything (other than the initial INSERT) a whole load harder than it should be.
For example, at some point in your app, you will need to modify roles: how are you going to remove role "3" from user 1616 and add "7" and "8" when he has "1, 5, 3, 7" or "3, 4, 5"?

It's much, much easier to fix your DB design error at this early stage than compound it by adding complicated (and hard to maintain) code to bodge round it!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900