0

Can any one suggest me how to get id of all children of a particular tree.

Table structure :

Table 1 : customer                         Table 2 : hierarchy
cId     hId                                hId            cId
1       1                                  1              null
2       2                                  2              null
3       3                                  3                2
4       4                                  4                3
5       5                                  5                2
6       6                                  6                5
7       7                                  7                7

Table (Customer) is the main element and table hierarchy only maintain the hierarchy of customer.

I have give cId 2 as root customer(parent). Now I have to get all cId which are child of cId 2 (Given).

Expected Result of above scenario will be 2,3,4,5,6 (2 already given and these id set is customer ids). This is Actual answer that a query should return.

I looked for recursive query, procedure and by join but I am unable to get this result.

Applicable for Mysql, postgresql and sql server

durron597
  • 31,968
  • 17
  • 99
  • 158
Bhim
  • 51
  • 8
  • *"I looked for recursive query, procedure and by join but I am unable to get this result."* Please show what you've tried so far. Also what's the Java tag for? You're asking for SQL... – m0skit0 Jun 09 '14 at 14:56
  • See http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree?rq=1 – Bill Karwin Jun 09 '14 at 15:18
  • http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree?rq=1. This link is useful, but how can we get pair of closure to insert in ClosureTable. main problem is to get closure. If I can get closure then it will be easy to get id of tree. But whta's the way to get pair of closure like (2,3) (2,4) (2,5) (2, 6). – Bhim Jun 09 '14 at 15:43

1 Answers1

1

You can use recursive queries with SQL Server using CTE (common table expressions).

Syntax is as follows:

WITH CTE
AS
(
    SELECT ID FROM HIERARCHY where ID =2
    UNION ALL
    SELECT ID FROM HIERARCHY child
    INNER JOIN CTE parent on child.parentid=parent.id
)
SELECT *
FROM CTE

Read more about it here: http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

Simcha Khabinsky
  • 1,970
  • 2
  • 19
  • 34
  • CTE is for only one table (for self referencing). But in my problem there is two table. id of customer is as foreign in hierarchy table as cId and hierarchy id in customer as hId. Look like cycle between two table (But there is no any cycle in above problem) and I have to given customer id 2 and i have to get all customer id which are child of customer id 2 – Bhim Jun 09 '14 at 15:09
  • What do you want the output to be? What column in `Customer` do you need to include in results. Your question states the result is just a list of `CustomerIDs`. – Simcha Khabinsky Jun 09 '14 at 16:15
  • You can always replace `SELECT * FROM CTE` with `select * from CUSTOMER WHERE ID in(SELECT distinct ID FROM CTE)` – Simcha Khabinsky Jun 09 '14 at 16:18