Click here to Skip to main content
15,035,283 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,
Table 1
UID		ROLE
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
3
4

IF UID=1
Then the O/P will be
Form Name 
1
2
4


What I have tried:

SELECT T1.FormName FROM 
Table 1 INNER JOIN Table 2 ON  T2.Role IN ('5','1')

WHERE T2.UID=2
Posted
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:
SQL
SELECT DISTINCT t2.FormName FROM Table2 t2
JOIN Table1 t1 ON t1.Role = t2.ROLE
WHERE t1.UID = 2
   
Comments
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