query on recursive table

Hi,
Suppose I'm working on a database containing 2 tables:
tStudent (student information)
========
|--|
| studentID | studentName | deptID |
|--|
| 504001 | John Doe | 10001 |
| 504005 | JI Jane | 200 |
|--|
tItems (organisational information)
======
|----|
| itemID | KindOfItem | itemName | parentID |
|----|
| 1 | top | top | <NULL> |
| 10 | school | Univ of Geel | 1 |
| 11 | school | Univ of Antwerp | 1 |
| 12 | school | Univ of Brussels | 1 |
| 100 | dept | science dept | 10 |
| 101 | dept | human science dept | 10 |
| 200 | dept | agriculture dept | 11 |
| 1001 | dept | computer science dept | 100 |
| 10001 | dept | Windows comp science dept | 1001 |
| 10002 | dept | Unix comp science dept | 1001 |
|----|
I need to write a query using Reporting Services on a MS SQL server that
gives me for every student the dept he is in (that's the easy part), but
also the top level school the department resides under. Result should be
like:
504001, John Doe , Windows comp science dept, Univ of Geel
504005, JI Jane, Agriculture dept, Univ of Antwerp
Can anybody give me any clues on how to do this (if possible)?
ErikWhy not add the parent_id for say Univ of Geel is 1 to a new column in the
first table and use an update to achieve this. Then you all you need is a
simple equi-join.
"dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:

> Hi,
> Suppose I'm working on a database containing 2 tables:
> tStudent (student information)
> ========
> |--|
> | studentID | studentName | deptID |
> |--|
> | 504001 | John Doe | 10001 |
> | 504005 | JI Jane | 200 |
> |--|
> tItems (organisational information)
> ======
> |----|
> | itemID | KindOfItem | itemName | parentID |
> |----|
> | 1 | top | top | <NULL> |
> | 10 | school | Univ of Geel | 1 |
> | 11 | school | Univ of Antwerp | 1 |
> | 12 | school | Univ of Brussels | 1 |
> | 100 | dept | science dept | 10 |
> | 101 | dept | human science dept | 10 |
> | 200 | dept | agriculture dept | 11 |
> | 1001 | dept | computer science dept | 100 |
> | 10001 | dept | Windows comp science dept | 1001 |
> | 10002 | dept | Unix comp science dept | 1001 |
> |----|
> I need to write a query using Reporting Services on a MS SQL server that
> gives me for every student the dept he is in (that's the easy part), but
> also the top level school the department resides under. Result should be
> like:
> 504001, John Doe , Windows comp science dept, Univ of Geel
> 504005, JI Jane, Agriculture dept, Univ of Antwerp
> Can anybody give me any clues on how to do this (if possible)?
> Erik
>|||You didn't specify if the dept of the recursion is variable. Assuming
it is not then:
select x.studentID, x.studentName, y.DeptName, y.School
from tStudent x,
(
select a.deptID, a.itemName as DeptName, b.itemName as School
from tItems a, tItems b
where a.parentID = b.deptID
) y
where x.deptID = y.deptID
Ranny

> Hi,
> Suppose I'm working on a database containing 2 tables:
> tStudent (student information)
> ========
> |--|
> | studentID | studentName | deptID |
> |--|
> | 504001 | John Doe | 10001 |
> | 504005 | JI Jane | 200 |
> |--|
> tItems (organisational information)
> ======
> |----|
> | itemID | KindOfItem | itemName | parentID |
> |----|
> | 1 | top | top | <NULL> |
> | 10 | school | Univ of Geel | 1 |
> | 11 | school | Univ of Antwerp | 1 |
> | 12 | school | Univ of Brussels | 1 |
> | 100 | dept | science dept | 10 |
> | 101 | dept | human science dept | 10 |
> | 200 | dept | agriculture dept | 11 |
> | 1001 | dept | computer science dept | 100 |
> | 10001 | dept | Windows comp science dept | 1001 |
> | 10002 | dept | Unix comp science dept | 1001 |
> |----|
> I need to write a query using Reporting Services on a MS SQL server that
> gives me for every student the dept he is in (that's the easy part), but
> also the top level school the department resides under. Result should be
> like:
> 504001, John Doe , Windows comp science dept, Univ of Geel
> 504005, JI Jane, Agriculture dept, Univ of Antwerp
> Can anybody give me any clues on how to do this (if possible)?
> Erik
User submitted from AEWNET (http://www.aewnet.com/)|||This smells more like homework instead of real world scenario, but I'll play
along! how about this:
set nocount on
declare @.organization table (itemid int, kindofitem varchar(20), itemname
varchar(30), parentid int, topparentid int)
declare @.studentinfo table (studentid int, studentname varchar(30), deptid
int)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(10, 'school', 'Univ of Geel', 1)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(11, 'school', 'Univ of Antwerp', 1)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(12, 'school', 'Univ of Brussels', 1)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(100, 'dept', 'science dept', 10)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(101, 'dept', 'human science dept', 10)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(200, 'dept', 'agriculture dept', 11)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(1001, 'dept', 'computer science dept', 100)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(10001, 'dept', 'Windows comp science dept', 1001)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(10002, 'dept', 'Unix comp science dept', 1001)
insert into @.studentinfo (studentid, studentname, deptid) values(504001,
'John Doe', 10001)
insert into @.studentinfo (studentid, studentname, deptid) values(504005, 'JI
Doe', 200)
update @.organization set topparentid = itemid where parentid = 1
while 1 = 1
begin
update @.organization
set topparentid = k.topparentid
from @.organization o
join (select itemid, topparentid from @.organization where topparentid is
not null) k
on o.parentid = k.itemid
where o.topparentid is null
if @.@.rowcount = 0
break
end
select * from @.organization
"dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:
> Hi,
> Suppose I'm working on a database containing 2 tables:
> tStudent (student information)
> ========
> |--|
> | studentID | studentName | deptID |
> |--|
> | 504001 | John Doe | 10001 |
> | 504005 | JI Jane | 200 |
> |--|
> tItems (organisational information)
> ======
> |----|
> | itemID | KindOfItem | itemName | parentID |
> |----|
> | 1 | top | top | <NULL> |
> | 10 | school | Univ of Geel | 1 |
> | 11 | school | Univ of Antwerp | 1 |
> | 12 | school | Univ of Brussels | 1 |
> | 100 | dept | science dept | 10 |
> | 101 | dept | human science dept | 10 |
> | 200 | dept | agriculture dept | 11 |
> | 1001 | dept | computer science dept | 100 |
> | 10001 | dept | Windows comp science dept | 1001 |
> | 10002 | dept | Unix comp science dept | 1001 |
> |----|
> I need to write a query using Reporting Services on a MS SQL server that
> gives me for every student the dept he is in (that's the easy part), but
> also the top level school the department resides under. Result should be
> like:
> 504001, John Doe , Windows comp science dept, Univ of Geel
> 504005, JI Jane, Agriculture dept, Univ of Antwerp
> Can anybody give me any clues on how to do this (if possible)?
> Erik
>|||@.aew_nospam.com schreef:
> You didn't specify if the dept of the recursion is variable. Assuming
> it is not then:
It IS variable, as you can see from the example:
Student JI Jane resides under dept "agriculture dept", that resides
directly under school "Univ of Antwerp".
Student John Doe resides under dept "Windows comp sc dept", that resides
under dept "computer science dept" that resides under dept "science
dept" that resides under school "Univ of Geel".

> select x.studentID, x.studentName, y.DeptName, y.School
> from tStudent x,
> (
> select a.deptID, a.itemName as DeptName, b.itemName as School
> from tItems a, tItems b
> where a.parentID = b.deptID
> ) y
> where x.deptID = y.deptID
> Ranny
>
>
>
> User submitted from AEWNET (http://www.aewnet.com/)|||
> This smells more like homework instead of real world scenario, but I'll pl
ay
> along! how about this:
Please have your nose checked, because this is no homework but
real-world scenario :) It is part of a student administration system
we're implementing in a group of schools.
My job is to create report(s) from the database. The report mentionned
in OP should have the data on the student, the dept he works in and the
school this dept belongs to. Hope it's clear now :)
> set nocount on
> declare @.organization table (itemid int, kindofitem varchar(20), itemname
> varchar(30), parentid int, topparentid int)
> declare @.studentinfo table (studentid int, studentname varchar(30), deptid
> int)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(10, 'school', 'Univ of Geel', 1)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(11, 'school', 'Univ of Antwerp', 1)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(12, 'school', 'Univ of Brussels', 1)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(100, 'dept', 'science dept', 10)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(101, 'dept', 'human science dept', 10)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(200, 'dept', 'agriculture dept', 11)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(1001, 'dept', 'computer science dept', 100)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(10001, 'dept', 'Windows comp science dept', 1001)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(10002, 'dept', 'Unix comp science dept', 1001)
> insert into @.studentinfo (studentid, studentname, deptid) values(504001,
> 'John Doe', 10001)
> insert into @.studentinfo (studentid, studentname, deptid) values(504005, '
JI
> Doe', 200)
> update @.organization set topparentid = itemid where parentid = 1
> while 1 = 1
> begin
> update @.organization
> set topparentid = k.topparentid
> from @.organization o
> join (select itemid, topparentid from @.organization where topparentid i
s
> not null) k
> on o.parentid = k.itemid
> where o.topparentid is null
> if @.@.rowcount = 0
> break
> end
> select * from @.organization
>
> "dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:
>|||marcmc schreef:
> Why not add the parent_id for say Univ of Geel is 1 to a new column in the
> first table and use an update to achieve this. Then you all you need is a
> simple equi-join.
Yes, that is true. But the information I need is available already in
the database, so adding the new column in the first table would make the
information redundant...
>
> "dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Have you thought about designing a normalized schema which does not mix
data and metadata? This is a better approach in the long run than
looking for kludges. Just from this posting and the silly 'T-"
prefixes that you are an OO programmer no training in SQL. OO models do
not work with SQL.