Correspond multiple values to a single username

Question

Post reply

Correspond multiple values to a single username

jashwantsingh

Valued Member

Points: 50
More actions
March 11, 2011 at 9:43 pm

#236161

Hello friends , I am newbie here.
In my project a user can have multiple friends and I need to store friends of a each user. What I am doing is that , in my table , I have created two columns ; one for username and second for friends.
When a user adds a new friend name 'newfriend', I update like this :
"update table set friend=friend+'newfriend|' where user=myuser"
Then, when I need to select different friends, I fetch friend column and split the value by '|' to get an array of friends.
This code is working fine. But I know, you geniuses have something better in mind.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

ColdCoffee

SSC-Dedicated

Points: 39972

March 11, 2011 at 9:57 pm

#1297965

What approach are u following to split, currently ?

jashwantsingh Valued Member Points: 50 More actions · Answer 2

I am spliting in front end (ASP.Net) by .Split('<char>') function of strings.

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 3

jashwantsingh (3/11/2011)
Hello friends , I am newbie here.
In my project a user can have multiple friends and I need to store friends of a each user. What I am doing is that , in my table , I have created two columns ; one for username and second for friends.
When a user adds a new friend name 'newfriend', I update like this :
"update table set friend=friend+'newfriend|' where user=myuser"
Then, when I need to select different friends, I fetch friend column and split the value by '|' to get an array of friends.
This code is working fine. But I know, you geniuses have something better in mind.

How do you remove a friend? Split it in the front end and then reassemble it except for the friend you want to remove?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jashwantsingh Valued Member Points: 50 More actions · Answer 4

Yeah...I will do the same...I will remove friend from front end.

I think, its not a good idea. Can you suggest something better ? How do you people deal with such type of problems ?

WayneS SSC Guru Points: 95461 More actions · Answer 5

jashwantsingh (3/11/2011)
Yeah...I will do the same...I will remove friend from front end.
I think, its not a good idea. Can you suggest something better ? How do you people deal with such type of problems ?

Basically, anytime you have delimited data in one column to represent multiple occurrences, then you should be looking at a child table. For instance (grossly simplified):

CREATE TABLE Users (UserID INT IDENTITY CONSTRAINT PK_Users PRIMARY KEY,

Name varchar(50) CONSTRAINT UQ_Users_Name UNIQUE);

CREATE TABLE UserFriends (UserID INT REFERENCES Users(UserID),

FriendName varchar(50),

CONSTRAINT PK_UserFriends PRIMARY KEY (UserID, FriendName));

INSERT INTO Users VALUES ('WayneS');

INSERT INTO UserFriends VALUES (1, 'jashwantsingh');

INSERT INTO UserFriends VALUES (1, 'Jeff Moden');

INSERT INTO UserFriends VALUES (1, 'ColdCoffee');

To add a new friend, just insert it into the UserFriends table. To remove a friend, just delete it.

To make your delimited list for the front end:

SELECT u.Name, Friends = STUFF((SELECT '|'+uf.FriendName

FROM UserFriends uf

WHERE uf.UserID = u.UserID

ORDER BY uf.FriendName

FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'')

FROM Users u;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 6

Everything is good there except I wouldn't make the delimited list for the front end. Just return a result set and consume it at the front end. I guess that's a matter of choice.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jashwantsingh Valued Member Points: 50 More actions · Answer 7

yeah...that would be better if I do processing at backend itself and just return to front end what is really needed. But my sql server skills really sucks...I can do simple select, insert, update commands ( the ones needed to learn sql).

If you can help me out in building such strong queries which can split string or can check if string possesses particular characters...or anything like that , which can help me.