Customer associations via multiple accounts

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

General SQL Server Forums

New to SQL Server Programming

Customer associations via multiple accounts

Author

Topic

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-12-21 : 14:59:30

Hi all, here's some background on my question: an account can have multiple people in it, but a person can have multiple accounts (and share those accounts with people not in his other accounts - I hope I'm making sense).

So say I have:


SELECT 11 AS c_id, 102 AS acct_id
INTO #sample_accounts
UNION SELECT 12, 102
UNION SELECT 14, 102
UNION SELECT 11, 101
UNION SELECT 12, 101
UNION SELECT 13, 103
UNION SELECT 14, 103
UNION SELECT 21, 201
UNION SELECT 22, 201

--DROP TABLE #sample_accounts

I want to have a way that would show me that customers 11, 12, 13, and 14 are associated with each other but not with customers 21 and 22. Ideally I'd like the output to look like:


c_id    acct_id (it doesn't matter which account, so long as it's consistent)
================
11      102
12      102
13      102
14      102
21      201
22      201

Any thoughts? I'm trying to figure out a way to do this without having to do a million self joins, but will be flexible enough to allow for multiple degrees of separation between customers.

Thanks! x 10^6

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 15:03:19

how do you want the values grouped by? because they are in a set of 10's?

What value is that?

>> it doesn't matter which account

Sure it does...which one do you want? Actually mixing data to other rows that it doesn't belong to is dangerous and misleading

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-12-21 : 16:24:23

quote:
Originally posted by X002548

how do you want the values grouped by? because they are in a set of 10's?

What value is that?

>> it doesn't matter which account

Sure it does...which one do you want? Actually mixing data to other rows that it doesn't belong to is dangerous and misleading

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Sorry, I numbered the customers and accounts in a way that would make it convenient for me to quickly see which ones were associated with each other. Also, I suppose I don't need them to be paired with an account number at all; I just need to know that a group of people are connected. Maybe create a group ID instead?

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 16:30:40

so they are connected if they share an account?

So 12 shares with 14..so then 14 shares with 13, so NOW 12 pairs with 13?

Yes?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 16:36:14

you need recursion

http://www.4guysfromrolla.com/webtech/071906-1.shtml

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-12-21 : 16:39:02

quote:
Originally posted by X002548

so they are connected if they share an account?

So 12 shares with 14..so then 14 shares with 13, so NOW 12 pairs with 13?

Yes?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Yes, that is correct. I'm not sure if this is the best way to group customers, but that's what the higher ups want, and I'm still too new at this job to question it (let alone give a better idea).

Sorry my explanation wasn't very clear. English is my second language.

It's also my first.

:(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources