0
OrganisationID OrganisationName     parentID 
    1                Org1             Null
    2                Org2              1
    3                Org3              1
    4                Org4              2
    5                Org5              2
    6                Org5              4

Table Name is tbl_Organisation

I am having a table similar to this. All I am trying is to retreive the Sub Organisation and display it. Suppose the Org ID passed is 3, then the Org3 doesnt have any child so it displays only Org3. Suppose if OrgID =2 then the Org2 has a child Org4 and Org4 has a child Org5. So for OrgID=2 I have to display Org2, Org4 and Or5. SO how can I do that. I have tried few things but it didn't work as I intended. 

SELECT distinct b.OrganisationID,b.OrganisationName 
FROM tbl_organisation as a LEFT OUTER JOIN tbl_organisation as b 
on a.OrganisationID=b.ParentID  where a.OrganisationID=b.parentID

Tell me where I am wrong I am using this in asp.net website, I am using c# and mysql

Mark
  • 2,720
  • 15
  • 56
  • 87
  • Check this out http://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join-in-sql-server – juharr Nov 02 '11 at 14:41

1 Answers1

0

This is related to Hierarchail Query:

SELECT (LPAD(' ', level * 3, ' ')||OrganisationID) as Org_id, OrganisationName, parentID, LEVEL FROM tbl_Organisation START WITH OrganisationID = ---

Comment: Pass the Organisation ID here

CONNECT BY PRIOR OrganisationID = parentID

If you pass 1 as the OrganizationID then the Output will be

Org_id OrganisationName parentID LEVEL 1 Org1 1 2 Org2 1 2 4 Org4 2 3 6 Org6 4 4 5 Org4 2 3 3 Org2 1 2

Remigious Mano
  • 3
  • 3