Find immediate parent based on condition

Question

1.00/5 (1 vote)

See more:

I need to find the immediate parent id based on the below condition:

|CustomerNum|ParentCustomerNum|IsPlatform|
|-----------|-----------------|----------|
|132        |0                |1         |
|253        |132              |0         |
|2041       |253              |0         |
|2132       |253              |0         |
|521        |132              |0         |
|551        |132              |0         |
|594        |132              |1         |
|5642       |5434             |1         |
|5811       |5434             |1         |

Above is the sql table entries. I do have below condition to find the record.
1.) find record based on CustomerNum, check if it has ParentCustomerNum greater then 0 and IsPlatform is 1.
2.) find record based on CustomerNum, check if it has ParentCustomerNum is 0 and IsPlatform is 1.
3.) find record based on CustomerNum where ParentCustomerNum is greater then 0 and IsPlatform is not 0, then find the parent record and check again if that record has ParentCustomerNum is greater then 0 and IsPlatform is not 0.

What I have tried:

I tried CTE but having no luck on that side, I am new to the sql part.

SQL

WITH cte AS 
(
    SELECT CustomerId, ISSCustId, CustomerName, CustomerNum, IsPlatform, CustomerNum As TopParent 
    FROM [dbo].[VwCustomer]
    --WHERE ParentCustomerNum = 0
    UNION ALL
    SELECT T.CustomerId, T.ISSCustId, T.CustomerName, 
        T.CustomerNum, T.IsPlatform, cte.TopParent
    FROM [dbo].[VwCustomer] As T
    JOIN cte 
        ON T.ParentCustomerNum = cte.CustomerNum
)

SELECT *
FROM cte
WHERE cte.CustomerNum = 253
ORDER BY TopParent DESC;

Expected values/query:

1. CustomerNum 594 has ParentCustomerNum as 132 and IsPlatform 1 (true). So, My query will stop here because IsPlatform is true. and will return this record only.
2. If for the above record IsPlatform is 0 (false) then query will traverse for CustomerNum 132 and will check if it has ParentCustomer and IsPlatform as false. @siggemannen let me know is any part is not clear. Thank you.

Posted 18-Feb-24 23:08pm

DT_2

Updated 19-Feb-24 3:35am

v3

Add a Solution

Comments

CHill60 19-Feb-24 9:20am

"I tried CTE but having no luck on that side" - post the code you tried and the results you got. Nobody is going to write this for you if you don't show some effort of your own

DT_2 19-Feb-24 9:33am

WITH cte AS
(
SELECT CustomerId, ISSCustId, CustomerName, CustomerNum, IsPlatform, CustomerNum As TopParent
FROM [dbo].[VwCustomer]
--WHERE ParentCustomerNum = 0
UNION ALL
SELECT T.CustomerId, T.ISSCustId, T.CustomerName,
T.CustomerNum, T.IsPlatform, cte.TopParent
FROM [dbo].[VwCustomer] As T
JOIN cte
ON T.ParentCustomerNum = cte.CustomerNum
)

SELECT *
FROM cte
WHERE cte.CustomerNum = 253
ORDER BY TopParent DESC;

DT_2 19-Feb-24 9:33am

this is giving the duplicate records. @CHill60

Maciej Los 19-Feb-24 13:14pm

Define "duplicate records"...

DT_2 19-Feb-24 23:01pm

Duplicate records means below
CustNum|IsPlatform|Parent
253 |0 |253
253 |0 |132
253 |0 |0

What I need is as below
CustNum|IsPlatform|Parent
253 |0 |132
132 |0 |0

[no name] 19-Feb-24 17:12pm

You would expend less mental energy if you just used C#; after reading it into memory. It's a "tree".

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2024-02-20T09:46:00

I'm no sure what you want to achieve...

I believe below example will help you to understand how to filter your data.

SQL

DECLARE @cn AS INT = 253;  -- 253  --594  --2132
DECLARE @ip AS INT = 1;
DECLARE @pcn AS INT = 0;

WITH cte AS 
(
    SELECT CustomerNum, ParentCustomerNum, IsPlatform, 1 Lvl 
    FROM VwCustomer
    WHERE CustomerNum = @cn
    UNION ALL
    SELECT T.CustomerNum, T.ParentCustomerNum, T.IsPlatform, Lvl + 1
    FROM VwCustomer As T
    INNER JOIN cte 
        ON T.CustomerNum = cte.ParentCustomerNum
)
SELECT *
FROM cte
--WHERE IsPlatform = @ip
--  AND ParentCustomerNum = @pcn;

Here is a "working sample": SQL Server 2022 | db<>fiddle[^]

As you can see, i added "Lvl" field which identifies the distance between initial value and top parent :)

Good luck!