# Need help to solve this scenario

26 Reputation points
2021-06-25T12:03:14.793+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions

1. 100.8K Reputation points MVP
2021-06-26T18:34:49.69+00:00

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.

1. 17,716 Reputation points
2021-06-25T19:14:07.707+00:00

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

2. 26 Reputation points
2021-06-26T17:45:13.533+00:00

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

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