Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2012
    Posts
    11

    Unanswered: Need a small help with parent child

    Hello,

    I have a simple problem.

    Table has 2 columns:

    A B
    B C
    C D
    D E
    F G
    G H
    I J

    The answer I need is:
    (the middle column is the count of separation between the parent and the last child)

    A 4 E
    F 2 H
    I 1 J

    Thank you for your help,

    Batsal

    Using sql server 2000


    create table parent_child (Col1 char(1), Col2 char(2));

    insert into parent_child values ('A','B');
    insert into parent_child values ('B','C');
    insert into parent_child values ('C','D');
    insert into parent_child values ('D','E');
    insert into parent_child values ('F','G');
    insert into parent_child values ('G','H');
    insert into parent_child values ('I','J');

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That took a bit

    So, You want to

    1. Find ALL top level Parents
    2. Determine the Path of all relationships until the end
    3. Count the number of relationships in the path

    Yes?

    Want to indicate what value this has to you?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser View Post
    Want to indicate what value this has to you?
    does it matter?

    i can think of one application... multi-level marketing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    edification

    This massage is too short
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2012
    Posts
    11

    a little help

    Yes, Brett you are correct.

    "Want to indicate what value this has to you?"

    This is for our clinical database. We make aliquots of the original sample, thaw it, give it another id, after some time thaw it again, and again.. At one point, we are interested on what the end id is, what was the original and how many times it has been thawed, as thawing sometime destroys the sample.

    i am not a db person, just trying to learn something new.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK..so you will need to use a "Common Table Expression" or CTE

    I gotta figure out the count thing, but here's a start

    Code:
    ;WITH TopParents
       AS (
       SELECT * 
         FROM parent_child WHERE Col1 NOT IN (SELECT Col2 FROM parent_child)
    UNION ALL
       SELECT pc.*
         FROM parent_child pc INNER JOIN TopParents tp ON pc.Col1 = tp.Col2
    )
    
    SELECT * FROM TopParents ORDER BY Col1
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2008
    Posts
    1

    suggest different approach to storing of data

    Hello,
    Perhaps, if you create a parent table -- let's say: "sample_origin" with some key column, date_created, originating_technician, etc.

    But then as samples are manipulated thawed, frozen, autoclaved, discarded, etc. : enter those events into a related table such as "Sample_events" with the sample key value, child_event_ID, event_type, datetime, duration, tech, etc.

    Then to monitor or report the history with a simple left outer join or other ways.

    Hope that might help.

    Best luck.

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    (Started on it before I saw Brett had already stared working on a solution.)
    This should work:
    Code:
    DROP table #parent_child;
    create table #parent_child (
    	Parent char(1), 
    	Child char(1)
    );
    
    insert into #parent_child(Parent, Child) values 
    ('A','B'),
    ('B','C'),
    ('C','D'),
    ('D','E'),
    ('F','G'),
    ('G','H'),
    ('I','J');
    
    ;WITH CTE AS
    (SELECT T1.Parent, T1.Child, 1 as steps
    FROM #parent_child as T1
    	LEFT OUTER JOIN #parent_child as T2 ON 
    		T1.Child = T2.Parent
    WHERE T2.Parent IS NULL 
    	UNION ALL
    SELECT #parent_child.Parent, CTE.Child, CTE.steps + 1
    FROM CTE
    	INNER JOIN #parent_child ON 
    		CTE.Parent = #parent_child.Child
    ),
    Ordered AS
    (SELECT Parent, 
    	Child, 
    	steps,
    	ROW_NUMBER() OVER (PARTITION BY child ORDER BY steps DESC) as RowNum
    FROM CTE
    )
    SELECT Parent, steps, Child
    FROM Ordered
    WHERE RowNum = 1
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I get by with a little help from my friends...
    etc

    Thanks Wim
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Mar 2012
    Posts
    11

    little help

    Thank you everyone for help.

    My only concern is I am using sql server 2000 and am getting errors while using the scrips.

    error: 'ROW_NUMBER' is not a recognized function name.

    also it seems to not like CTEs

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bd532 View Post
    error: 'ROW_NUMBER' is not a recognized function name.

    also it seems to not like CTEs
    amazing that everybody missed the fact that you clearly stated sql server 2000 in your first post



    you could just string a series of left joins together

    how many levels of separation do you expect, as a maximum? your sample data shows 4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Good Morning Rudy

    D'oh

    Why are you still on 2k?

    you can do as Rudy suggests, or you can use a cursor like we did in the old days and build denormalized rows that do all of the for you
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I get by with a little help from my friends...
    etc

    Thanks Wim
    You're welcome, Brett.
    amazing that everybody missed the fact that you clearly stated sql server 2000 in your first post
    I read "Need a small help", "sql server 2000" and a recursive problem. Only now I remember I thought at the time there was far too much conflicting information in that single post to get me started.
    Quote Originally Posted by Brett Kaiser View Post
    you can do as Rudy suggests, or you can use a cursor like we did in the old days and build denormalized rows that do all of the for you
    Only sissies use recursive SQL, Real Men do it with cursors, and lists, and loops, as God intended.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  14. #14
    Join Date
    Mar 2012
    Posts
    11

    thanks for the help

    Quote Originally Posted by Wim View Post
    You're welcome, Brett.I read "Need a small help", "sql server 2000" and a recursive problem. Only now I remember I thought at the time there was far too much conflicting information in that single post to get me started.
    Only sissies use recursive SQL, Real Men do it with cursors, and lists, and loops, as God intended.
    Thank you everyone for your input.

    The reason I am using 2000 is our company is too cheap to get 2005

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bd532 View Post
    The reason I am using 2000 is our company is too cheap to get 2005
    so did you go the left outer joins route?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •