Sql Select Query For Organization Tree (hierarchy)

I have a table like this; CREATE TABLE [dbo].[TH_ORGANIZATION] ( [ID_CORGANIZATION] [decimal](18, 0) IDENTITY(1,1) NOT NULL, [ID_CCOMPANY] [nvarchar](10) NOT NULL, [COR

Solution 1:

SELECT * FROM TH_ORGANIZATION As O,
    TH_ORGANIZATION AsSubWHERE O.ID_CORGANIZATION 
    = Sub.CORGANIZATION_UPLINK_ID;

You will get a list of Organization with its sub organizations, recursive. Of course you can order the list, too.

If you want all under one column you can use an outer join like this (so select only O.*):

SELECT O.* FROM th_organization As O
LEFT OUTER JOIN th_organization AsSubON O.id_corganization 
    = Sub.corganization_uplink_id;

Example (I use abbreviations), you should get this kind of list:

id_c uplink_id  name
  1             OrgA
  2             OrgB 
 11      1      subA
 12      1      subB
 21      2      sub2
111     11      subsubA

Solution 2:

My best solution that I find :

WITH temp as(SELECT*FROM TH_ORGANIZATION WHERE ID_CORGANIZATION ='3'UNIONALLSELECT ei.*FROM TH_ORGANIZATION ei INNERJOIN temp x ON ei.CORGANIZATION_UPLINK_ID = x.ID_CORGANIZATION ") SELECT * FROM temp

Note: '3' my started organization id