Following a chain of IDs - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Following a chain of IDs

Author

Topic

thenearfuture
Starting Member

35 Posts

Posted - 2010-08-20 : 13:59:04

Hi,

I've run into a challenge that I hope someone can help with.

There is a Customers table with a CustomerId and a RealCustomerId.


 .-------------------------------------.
  |            CUSTOMERS                |
  +--------+---------------+------------+
  | CustId | CustName      | RealCustId |
  +--------+---------------+------------+
  |  1 	   | A             |  1         |
  +--------+---------------+------------+
  |  2 	   | B             |  2         |
  +--------+---------------+------------+
  |  3 	   | C             |  5         |
  +--------+---------------+------------+
  |  4 	   | D             |  7         |
  +--------+---------------+------------+
  |  5 	   | E             |  5         |
  +--------+---------------+------------+
  |  6 	   | F             |  6         |
  +--------+---------------+------------+
  |  7 	   | G             |  8         |
  +--------+---------------+------------+
  |  8 	   | H             |  9         |
  +--------+---------------+------------+
  |  9 	   | I             |  9         |
  +--------+---------------+------------+
  |  10    | J             |  10        |
  +--------+---------------+------------+

Usually, the CustomerId is the same as the RealCustomerId. Sometimes, though, one Customer gets bought by another Customer and the purchasing CustomerId is inserted into the original Customer's RealCustomerId.

The process isn't perfect, so it's possible for a chain of mergers to happen and for some RealCustomerIds to be out of date.

Let's take CustId 4 as an example from the "Customers" table above.

CustId 4 was bought by CustId 7

CustId 7 was bought by CustId 8

CustId 8 was bought by CustId 9

The RealCustomerId was never updated for CustId 4 to show the newest owner - CustId 9 - and there is no easy way of tracking all the changes.

I need to see this chain of events so that ALL the subsequent CustIds are linked to the original CustId, as shown in the "Anticipated Results" table below.




  .-------------------------------------.
  |       ANTICIPATED RESULTs           |
  +--------+---------------+------------+
  | CustId | CustName      | RealCustId |
  +--------+---------------+------------+
  |  1 	   | A             |  1         |
  +--------+---------------+------------+
  |  2 	   | B             |  2         |
  +--------+---------------+------------+
  |  3 	   | C             |  5         |
  +--------+---------------+------------+
  |  4 	   | D             |  7         |
  +--------+---------------+------------+
  |  4 	   | D             |  8         |
  +--------+---------------+------------+
  |  4 	   | D             |  9         |
  +--------+---------------+------------+
  |  5 	   | E             |  5         |
  +--------+---------------+------------+
  |  6 	   | F             |  6         |
  +--------+---------------+------------+
  |  7 	   | G             |  8         |
  +--------+---------------+------------+
  |  7 	   | G             |  9         |
  +--------+---------------+------------+
  |  8 	   | H             |  9         |
  +--------+---------------+------------+
  |  9 	   | I             |  9         |
  +--------+---------------+------------+
  |  10    | J             |  10        |
  +--------+---------------+------------+

CustId 4 would have three rows, each displaying one of the purchasing CustomerIds. In the real dataset, there could be any number of mergers and this chain could last over 3 CustIds or 20 CustIds.

Any ideas?

Thank you for your help.

Here is the sample code:


CREATE TABLE #Customers 

	(CustId INT,
	CustName VARCHAR(1),
	RealCustId INT)

INSERT INTO #Customers 
SELECT 1, 'A', 1 UNION ALL
SELECT 2, 'B', 2 UNION ALL
SELECT 3, 'C', 5 UNION ALL
SELECT 4, 'D', 7 UNION ALL
SELECT 5, 'E', 5 UNION ALL
SELECT 6, 'F', 6 UNION ALL
SELECT 7, 'G', 8 UNION ALL
SELECT 8, 'H', 9 UNION ALL
SELECT 9, 'I', 9 UNION ALL
SELECT 10, 'J', 10 


CREATE TABLE #AnticipatedResults
(CustId INT,
RealCustId INT) 

INSERT INTO #AnticipatedResults
SELECT 1,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,5 UNION ALL
SELECT 4,7 UNION ALL
SELECT 4,8 UNION ALL
SELECT 4,9 UNION ALL
SELECT 5,5 UNION ALL
SELECT 6,6 UNION ALL
SELECT 7,8 UNION ALL
SELECT 7,9 UNION ALL
SELECT 8,9 UNION ALL
SELECT 9,9 UNION ALL
SELECT 10,10

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 14:26:27

Use recursive CTE for getting this. see BOL for details

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources