Sql query to fetch multiple roles

Question

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 3-May-21 21:38pm

Member 11658469

Updated 3-May-21 21:51pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2021-05-03T21:51:00

Solution 1

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

Posted 3-May-21 21:51pm

OriginalGriff

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!