Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
 Posted - 2010-08-01 : 11:51:31
|
| Hi Team,I have table as follows – (I agree that it is not a good table design; but I have no option to change it)The areas in which a user has membership are stored as a comma separated value. I need to find out all the users who have got membership in the area “a”. Can we achieve this without using a WHILE loop. If yes, please help.DECLARE @User TABLE (userid INT, areas VARCHAR(MAX))INSERT INTO @User (userid , areas ) VALUES (1,'a,df,aa')INSERT INTO @User (userid , areas ) VALUES (2,'aa')INSERT INTO @User (userid , areas ) VALUES (3,'aaa')INSERT INTO @User (userid , areas ) VALUES (4,'tom')INSERT INTO @User (userid , areas ) VALUES (5,'a,tf')SELECT * FROM @UserExpected result :: 1 and 5ThanksLijo |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-01 : 13:30:06
|
SELECT * FROM @Userwhere ','+areas+',' like '%,a,%'
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
Posted - 2010-08-02 : 00:42:38
|
Thanks. That's why I post questions here - ingenious answers always  Once again, thanks. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-08-02 : 01:57:04
|
I wonder what should be the resultset?DECLARE @User TABLE (userid INT, areas VARCHAR(MAX))INSERT INTO @User (userid , areas ) VALUES (1,'a,df,aa')INSERT INTO @User (userid , areas ) VALUES (2,'aa')INSERT INTO @User (userid , areas ) VALUES (3,'aa,a')INSERT INTO @User (userid , areas ) VALUES (4,'tom')INSERT INTO @User (userid , areas ) VALUES (5,'a,tf')SELECT * FROM @Userwhere ','+areas+',' like '%,a,%'SELECT * FROM @User where areas like 'a,%' Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-02 : 05:55:30
|
Didn't get you.In this case it should be 1,3,5.What's the problem?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-08-02 : 09:11:09
|
| But the OP wanted find out all the users who have got membership in the area “a”I wonder why should id 3 come in resultset?I am sorry if I am wrong in understanding.Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-02 : 09:57:25
|
Because you have changed the OP example from (3,'aaa') to (3,'aa,a').So now id 3 should be in the result set.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-08-02 : 11:58:36
|
| So that is the reason I am asking why "aa,a" i.e id 3 should come when OP has asked just for "a" & not "aa,a" or "aaaa,a"Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-02 : 12:03:28
|
quote:
& not "aa,a" or "aaaa,a"
Sorry but OP hasn't asked for that...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-08-02 : 13:32:13
|
quote:
Originally posted by webfred
quote:
& not "aa,a" or "aaaa,a"
Sorry but OP hasn't asked for that...
No, you're never too old to Yak'n'Roll if you're too young to die.
But he did ask thatfind out all the users who have got membership in the area “a”Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-02 : 13:35:25
|
So what?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-08-02 : 13:54:26
|
quote:
Originally posted by webfred
So what?
No, you're never too old to Yak'n'Roll if you're too young to die.
Forget it.Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-02 : 13:58:42
|
Please don't be peeved - ok
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-08-02 : 13:59:15
|
| The column holds multiple values separated by a comma. So "b,a" and "a,b" are the same set and both have the value "a" contained in said set. Thus "a,df,aa", "aa,a" and "a,tf" all contain the value "a." So, as Webfred said, in this case, that'd be the IDs 1,3,5. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-08-02 : 14:11:39
|
| please see with what OP posted I need to find out all the users who have got membership in the area “a”.The sample data he posted shows that the first value before the first comma of the data is called membership.So if the user has 2 values say 'a,b' & 'aa,b' then 2 data's are totally different.One is membership 'a' & other one is membership 'aa'So now with OP's need he wants to find all the records who has membership with 'a'.So returning the record 'aa,b' does not make sense.Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-02 : 14:22:54
|
quote:
The sample data he posted shows that the first value before the first comma of the data is called membership.
Aha, I think there we are going different ways!I believe:Each comma-separated-value is a membership.So if the value is "aa,a" then the user has two memberships/areas.Hence it is ok then to retrieve this user when searching for area="a".
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|
|
Querying a column with comma separated values