Need help to solve this scenario

Question

Hi All,

I need to write a recursive code but can't find the solution yet.

Scenario:

1. There is a table (ERPCustomer) that has 2 columns ERPCustomerBK, SF_Account
2. Sample Values
   ERPCustomer_BK, SF_AccountID
   AXPT5|75388|US-CA 0011400001a9G6IAAU
   AXPT5|74334|US-CA 001a000001BCKyXAAX

I need to find out the SF_AccountID for 1 ERPCustomer_BK.
"AXPT5|75388|US-CA" BK I have 2 SF_Account in my table "0011400001a9G6IAAU", "001a000001BCKyXAAX"

Now I need to check for these 2 SF_AccountID do we have any other ERPCustomer_BK in the data

This should go upto no further data found either for ERPCustomer_BK OR SF_AccountID.

Answer 1

As Ronen said, you went a little overboard that with the test data. For most questions of this type we rarely need more than 20 rows.

Anyway, here is a query, which may do what you are looking for. This is a recursive CTE, and the extra column datastr serves to prune combinations we have already found.

WITH rekurs AS (
   SELECT erpcustomer_bk, sf_accountID, 
          cast(erpcustomer_bk + ' ' + sf_accountID as nvarchar(MAX)) AS datastr
   FROM   temp.Erpx 
   WHERE  erpcustomer_bk = '5|75388|US-CA' 
   UNION ALL
   SELECT E.erpcustomer_bk, E.sf_accountID, 
          r.datastr + '///' + E.erpcustomer_bk + ' ' + E.sf_accountID
   FROM   rekurs r
   JOIN   temp.Erpx E ON E.erpcustomer_bk = r.erpcustomer_bk OR
                         E.sf_accountID   = r.sf_accountID
   WHERE  charindex(E.erpcustomer_bk + ' ' + E.sf_accountID, r.datastr) = 0
)   
SELECT erpcustomer_bk, sf_accountID
FROM  rekurs

I am not sure that this is the most efficient solution. Maybe it is better to run a loop where you store the partial results in a temp table. Then you can toggle on which column you are making lookups on. But I leave it to others to explore that area.

Answer 2

You appear to be saying you have a parent/child relationship.

I suggest you start here:
https://www.codeproject.com/Articles/818694/SQL-Queries-to-Manage-Hierarchical-or-Parent-child

Answer 3

Please find the attached zipped file for DDL and DML script.
Table name: ERPX

you can try for ERPCustomer_BK ='5|75388|US-CA'

109585-xx5.txt