.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> SQL Query >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Getting childs for hiearchical relationships

Posted By:Maverick       Posted Date: March 16, 2011    Points: 40    Category: SQL Query    URL: http://www.dotnetspark.com  

If u have a self referenceing table in you database then the function will help you to get all of it child nodes
 

Create the table first execute the script
CREATE TABLE EMPLOYEES1
(
  EMPID  INTEGER,
  NAME   VARCHAR2(20 BYTE)                      NOT NULL,
  MGRID  INTEGER
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX EMPLOYEES1_PK ON EMPLOYEES1
(EMPID)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE EMPLOYEES1 ADD (
  CONSTRAINT EMPLOYEES1_PK
 PRIMARY KEY
 (EMPID)
    USING INDEX
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE EMPLOYEES1 ADD (
  FOREIGN KEY (MGRID)
 REFERENCES EMPLOYEES1 (EMPID));


SET DEFINE OFF;
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (6, 'ram', NULL);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (5, 'sam', 6);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (4, 'nikhil', 6);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (2, 'ben', 5);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (3, 'tom', 5);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (1, 'kapil', 4);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (9, 'ashsh', 2);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (8, 'payal', 3);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (10, 'sudha', 3);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (7, 'nirmal', NULL);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (16, 'deep', NULL);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (15, 'maninder', 16);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (14, 'hardik', 16);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (11, 'karun', 14);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (17, 'hetal', 11);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (18, 'aashal', 11);
Insert into EMPLOYEES1
   (EMPID, NAME, MGRID)
 Values
   (20, 'bhoomi', 18);
COMMIT;


Exceute the following function

Use Employees1=@tblname, Empid=@pcol, MgrId=@ccol, Select empid from emplyees1 where empid=6

GO
/****** Object:  StoredProcedure [dbo].[getallreferencechilds]    Script Date: 04/22/2009 14:45:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[getallreferencechilds]
(
@tblname varchar(100),
@pcol varchar(100),
@ccol varchar(100),
@qry varchar(1000)
)
as

declare
@strcreateparent varchar(8000)


BEGIN

set @strcreateparent ='WITH GetChild('+ @ccol +','+ @pcol +', depth, sortcol)
AS
(
 
  SELECT '+ @ccol +','+ @pcol +', 0,
  CAST('+ @ccol +' AS VARBINARY(900))
  FROM '+ @tblname +'
  WHERE '+ @ccol +' in ('+ @qry +')

UNION ALL
 
 
SELECT E.'+ @ccol +',  E.'+ @pcol +', M.depth+1,
    CAST(sortcol + CAST(E.'+ @ccol +' AS BINARY(4)) AS VARBINARY(900))
  FROM '+ @tblname +' AS E
    JOIN GetChild AS M
      ON E.'+ @pcol +' = M.'+ @ccol +'
)


SELECT
    distinct '+ @ccol +' 
FROM GetChild  where depth not in(0) '

print @strcreateparent
exec (@strcreateparent)

END



     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend