update statement trouble - weird relationship

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

update statement trouble - weird relationship

Author

Topic

ShooterJ07
Starting Member

17 Posts

Posted - 2007-08-01 : 12:02:07

First.. thanks to all who post here. Big help!

Second.. I know this seems really messed up. It's very poor design. Unfortunately, I cannot edit the program, and the following is my only solution.

-----

I have a table that consist of 3 fields: RelationshipID, Person1, Person2. Example:

-------
RelationshipID,Person1,Person2
1,Joe,Bill
2,Joe,Bob
3,Bill,Joe
4,Bill,Bob
5,Bob,Joe
6,Bob,Bill
-------

Basically, this is a "friends list". Person1 is the user, and Person2 is his friend. So in this situation, everyone would appear on everyone else's friends list. If record #6 did not exist, then Bill would not appear on Bob's friends list but Bob would still appear on Bill's (because of record #4).

Yes.. I know.. this is pathetic. :(

Now, when a new user is added (we'll say Jane), I need to add that user to all of the previously existing users, as well as add all of the previously existing users to the new user.

So after adding Jane, my new table should be:

-------
RelationshipID,Person1,Person2
1,Joe,Bill
2,Joe,Bob
3,Bill,Joe
4,Bill,Bob
5,Bob,Joe
6,Bob,Bill
7,Joe,Jane
8,Bill,Jane
9,Bob,Jane
10,Jane,Joe
11,Jane,Bill
12,Jane,Bob
-------

The # of rows is always N*(N-1), where N=# of users. This is going to be ugly when we have 100 users. It makes no difference at all what order the records are in. The RelationshipID is simply an identifier & has no meaning.

Can anyone help me figure out the update statement(s) that would perform this update properly? I'm at a real loss & don't even know where to begin.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-01 : 12:10:10

It is going to get ugly and the table will explode with more number of users...

(1) Get a DISTINCT Person1's and do an INSERT with the DISTINCT Person1, Jane. This wil give you all the users with Jane as their friend
(2) do an INSERT with Jane,each of the distinct Person1's. This will add all the users to Jane.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ShooterJ07
Starting Member

17 Posts

Posted - 2007-08-01 : 12:18:50

Thanks for the reply dinakar.

Yes.. table will get very ugly with the # of users. Fortunately, I don't think it will be an issue since our # of users will always be pretty low.

Sorry to sound dumb (but I am)... can you elaborate on your suggestion any more? I'm not sure I understand..

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-01 : 12:26:52

(1) To add Jane to other users:


INSERT INTO YourTable
SELECT DISTINCT PErsonId, 'Jane'
FROM YourTable

(2) To add users to Jane


INSERT INTO YourTable
SELECT DISTINCT 'Jane', Person1
FROM YourTable

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ShooterJ07
Starting Member

17 Posts

Posted - 2007-08-01 : 12:39:59

Wow.. much simpler than I was making it out to be & should have been obvious. Sorry for the trouble, but thanks for the help. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources