.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 parents 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 parent nodes
 

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;

---execute the following function
Use Employees1=@tblname, Empid=@pcol, MgrId=@ccol, Select empid from emplyees1 where empid=6



(Query Section)

GO
/****** Object:  StoredProcedure [dbo].[getallreferenceparents]    Script Date: 04/22/2009 14:47:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





CREATE procedure [dbo].[getallreferenceparents]
(
@tblname varchar(100),
@pcol varchar(100),
@ccol varchar(100),
@qry varchar(1000)
)
as

declare
@strcreateparent varchar(8000)


BEGIN

set @strcreateparent = 'With GetParent('+ @ccol +','+ @pcol +',l) as
(

Select '+ @ccol +','+ @pcol +',0 from '+ @tblname +' Where '+ @ccol +' in('+ @qry +')
UNION ALL

Select E.'+ @ccol +',E.'+ @pcol +',l+1 from '+ @tblname +' E
 Join GetParent M
On E.'+ @ccol +' = M.'+ @pcol +'
)                       
select distinct '+ @ccol +'  from GetParent where l 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