Where condition with Comma Seperated Column Values

Question

0.00/5 (No votes)

See more:

Hello,

I have tables in sql as per below

Usermaster
Username | Group

Group Column have comma seperated groups in which user belong too.

Another table with name FolderPermission
FolderName | Username | GroupName

Now I want all the distinct foldername from FolderPermission Table when a user login to the application.

I have tried using IN query for it but when a user is in a group and I assign group permission to folder it is not showing it as it is a comma seperated value.
below is the query I tried

SQL

Select Distinct(FolderName) from FolderPermission 
Where UserName like '%USERNAME_GOES_HERE%' 
or GroupName in (select GroupName from UserMaster where Username='USERNAME_GOES_HERE')

Posted 17-Dec-15 22:39pm

bhavikadb

Updated 17-Dec-15 23:32pm

v3

Add a Solution

Comments

deepankarbhatnagar 18-Dec-15 5:00am

If you want all the distinct foldername from FolderPermission where dont you use distinct keyword. there is no use of groprnames here.
Write this query:

select distinct foldername from folder permission

bhavikadb 18-Dec-15 5:15am

I want foldername when a user login to the application

deepankarbhatnagar 18-Dec-15 5:21am

So how is usermaster realate to user login?

bhavikadb 18-Dec-15 5:27am

it has password field as well but I have only written needed columns for query

CHill60 18-Dec-15 5:19am

Some sample data and expected results would help use to work out how to change your query.
Aside from that, this is a truly awful database design - you should never store data as comma separated strings. Really.

bhavikadb 18-Dec-15 5:33am

Added Example and Expected Result

2 solutions

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)

CHill60 · Answer 1 · 2015-12-18T00:15:00

As I said in my comment, this is a truly awful database design. Do not store data as comma separated strings.

Given what you currently have, to get the data in the format you want try this

DECLARE @results VARCHAR(MAX) = null

SELECT @results = COALESCE(@results+',' , '') + FolderName
FROM
(
	Select Distinct(FolderName) as FolderName from FolderPermission 
	Where UserName like '%user3%' 
	or GroupName in (select GroupName from UserMaster where Username='user3')
) src
SELECT @results

Note that I've given Distinct(FolderName) a column name which is the only change to your original query above.

OR
a far better idea would be to do this before you display the results in whatever application is calling this.

OriginalGriff · Answer 2 · 2015-12-18T00:22:00

The problem is that your table design is flawed: you have stored multiple values in a column, separated by commas. This is a problem, as SQL is not good at "deCSVing" data.

Create a new table which contains the Groups:

ID    GroupName
1     Group1
2     Group2
3     Group3
...

Change your UserMaster table to store multiple Group entries for each user, referenced by the GroupID as a foreign key:

Username    Group
user1           1
user1           2
user1           3
user2           2
user2           4
user3           5

(Better still, have a table with UserID and UserName and reference that throughout all your tables as a foreign key.)
You then just fetch your data using IN and JOIN statements.

It seems like a lot of extra work, but it really isn't that difficult, and it saves you huge amounts of time, effort, and redundantly duplicated data later.

Where condition with Comma Seperated Column Values

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0