asked on

SQL Parent/Child query

Hi all

I have a table named "Nodes" with the following columns: "companyID", "parentID"

In this table a "Parent/Child" relationship is kept, in order to know which companies belong to who.

There can be many levels while creating the compnay structure based on the data of the table.

Think of the following scenario:

The top level company id = 1 (has no parent)

1 has 3 children with the following IDs: 10, 50, 80

50 has 2 children with the following IDs: 200, 400

200 has 1 child with the following ID: 5000

So, what i need is an efficient query to return as fast as possible, all the parents and children (whole structure) of a given company ID.

For example, given the ID:200 query should return the following results (two columns: companyID AND its parentID):

column A column B
50 1
400 50
5000 200
10 1
80 1
companyStructure.bmp

Pavel Celba

Two questions:
1) Why the result does not contain 200 - 50 combination?
2) Do you have SQL Server 2005? If yes then it is simple usage of CTE - you have to find the top most company for given child and then to select the whole tree for this company.

AJKBOC

ASKER

1) The result should contain 200 - 50 combination also. My mistake.
2) YES i have SQL Server 2005. However am not sure what happens with your proposed solution, when a child has more than one parents.

mazher

The result should be 1, 50 , 200 , 5000 becasue you are extracting parent child hierarchy of 200 compnay
i am sending you solution for this .

DECLARE @tbl TABLE ( ID INT , PID INT )
INSERT  INTO @tbl
        SELECT  1
              , NULL
        UNION ALL
        SELECT  10
              , 1
        UNION ALL
        SELECT  50
              , 1
        UNION ALL
        SELECT  80
              , 1
        UNION ALL
        SELECT  200
              , 50
        UNION ALL
        SELECT  400
              , 50
        UNION ALL
        SELECT  5000
              , 200 ; 
 
WITH    Childcte
          AS ( SELECT   *
               FROM     @tbl
               WHERE    id = 200
               UNION ALL
               SELECT   p.*
               FROM     @tbl p
               INNER JOIN Childcte c
               ON       c.id = p.pid
             ) ,
        parentcte
          AS ( SELECT   *
               FROM     @tbl
               WHERE    id = 200
               UNION ALL
               SELECT   p.*
               FROM     @tbl p
               INNER JOIN parentcte c
               ON       c.pid = p.id
             )
    SELECT  *
    FROM    Childcte
    UNION
    SELECT  *
    FROM    parentcte

Open in new window

Pavel Celba

Hmm, you didn't mention two parents possibility in the original question... You also didn't mention cyclic relation possibility (company 5000 can be a parent of company 1). What other possibilities exist?

AJKBOC

ASKER

Dear mazher, thanks for your query. However these are not the desired results. I need to get the whole structure, i need every company related.

Dear pcelba, yes you are right i havent mention it earlier. It is possible that cyclic relation may exists, as you have stated.
Including this possibility, i think am covered.

Pavel Celba

So, if you have the cyclic relation possibility then you may forgot about "efficient query" because you have to go through your structure in a loop and test each node if it was investigated already or not. You'll need stored procedure to do this work.

AJKBOC

ASKER

I thought of that.. Can you give me some pseudocode on this?

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

mazher

membership