p/c selfjoin question

Hi,
I need some help on a specific self join question.
I have a parent/child table:
CREATE TABLE [TAB_PC_KST] (
[Element] [varchar] (255) NULL ,
[Parent] [varchar] (255) NULL ,
[EType] [char] (1) NULL ,
[Weight] [float] NULL ,
) ON [PRIMARY]
GO
where EType is the element type and can be 'C' (for consoloidated element)
or 'N' (non consolidated element).
Lets say in that table is the following content:
Element Parent EType Weight
All Members <NULL> C <Null>
First Child All Members C 1
Second Child All Members N 1
First Sub First Child N 1
Second Sub First Child N 1
That will give a structure like the following
All Members
|--First Child
|--First Sub
|--Second Sub
|--Second Child
What I need is a select on that table, with the following result set
EType Element Weight
C All Members <NULL>
<NULL> First Child 1
<NULL> Second Child 1
C First Child <NULL>
<NULL> First Sub 1
<NULL> Second Sub 1
Is that possible without an cursor on all C-Elements? Perhaps with selfjoin
or
subqueries?
Thanks for any help!
Cheers
ThomasThomas
Look at greate script (example) written by Itzik Ben-Gan
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Thomas Seidel" <tseidel@.misag.com> wrote in message
news:%234qjvclSFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I need some help on a specific self join question.
> I have a parent/child table:
> CREATE TABLE [TAB_PC_KST] (
> [Element] [varchar] (255) NULL ,
> [Parent] [varchar] (255) NULL ,
> [EType] [char] (1) NULL ,
> [Weight] [float] NULL ,
> ) ON [PRIMARY]
> GO
> where EType is the element type and can be 'C' (for consoloidated element)
> or 'N' (non consolidated element).
> Lets say in that table is the following content:
> Element Parent EType Weight
> All Members <NULL> C <Null>
> First Child All Members C 1
> Second Child All Members N 1
> First Sub First Child N 1
> Second Sub First Child N 1
> That will give a structure like the following
> All Members
> |--First Child
> |--First Sub
> |--Second Sub
> |--Second Child
> What I need is a select on that table, with the following result set
> EType Element Weight
> C All Members <NULL>
> <NULL> First Child 1
> <NULL> Second Child 1
> C First Child <NULL>
> <NULL> First Sub 1
> <NULL> Second Sub 1
> Is that possible without an cursor on all C-Elements? Perhaps with
selfjoin
> or
> subqueries?
> Thanks for any help!
>
> Cheers
> Thomas
>|||On Tue, 26 Apr 2005 14:04:23 +0200, Thomas Seidel wrote:
(snip)
>What I need is a select on that table, with the following result set
>EType Element Weight
>C All Members <NULL>
><NULL> First Child 1
><NULL> Second Child 1
>C First Child <NULL>
><NULL> First Sub 1
><NULL> Second Sub 1
>Is that possible without an cursor on all C-Elements? Perhaps with selfjoin
>or
>subqueries?
Hi Thomas,
The query below will not return the exact same set as above; the
differrence is basically formatting that's better done at the client side.
(It can be done in SQL - but it ain't pretty and it won't run quick)
SELECT a.Element, b.Element, b.Weight
FROM TAB_PC_KST AS a
INNER JOIN TAB_PC_KST AS b
ON b.Parent = a.Element
WHERE a.EType = 'C'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)