SQLTeam.com | Weblogs | Forums

How to get parent ID as 1 and child as 0 in tree structure sql

Hi ...How can get a query to get parent as 1 and the child as 0 as shown below...
123

Please help me...Thank You

Welcome!

--create the sample data in usable form; 
--this is extremely helpful for people trying to answer your q,
--please try to provide this in future qs.

CREATE TABLE #trees (
    tree varchar(40) NOT NULL PRIMARY KEY,
    REQDATA bit NULL
    )
INSERT INTO #trees ( tree ) VALUES
    ('1'),('1.1'),('1.2'),('1.2.1'),
    ('1.2.1.1'),('1.2.1.1.1'),('1.2.1.1.2'),('1.2.1.1.3'),
    ('1.2.1.2'),('1.2.1.2.1'),('1.2.1.2.2'),('1.2.1.2.3'),
    ('1.3'),('1.3.1'),('1.3.2'),('1.4'),
    ('1.5'),('1.6'),('1.7')

--now my actual UPDATE attempt, please see if it does what you need
UPDATE t1
SET REQDATA = CASE 
        WHEN LEN(tree) - LEN(REPLACE(tree, '.', '')) <=1 OR /*level 1 and 2 values are always 1*/
        EXISTS(SELECT 1 FROM #trees t2 WHERE t2.tree LIKE t1.tree + '.%') THEN 1 
        ELSE 0 END
FROM #trees t1
1 Like

Thank You very much Scott....It worked like charm....
Sorry for not providing sample data ......In future for sure i will provide....