I have three tables as follows:
Table1:
ClientID StudentID Name
--
1 22 John
2 23 Chen
3 34 Ted
4 22 Bod
Primary Key=ClientID + StudentID
Table2:
InternalID ClientID StudentID Name
---
001 5 22 John
002 2 23 Chen
Primary Key=InternalID
Unique Constraint= ClientID + StudentID
Table3:
InternalID ClientID StudentID Name DateTransfered
---
001 1 22 John 2/2/05
Primary Key=InternalID
Unique Constraint= ClientID + StudentID
Here is what I'd like to do:
I'd like to check every single record in Table1 with Table2 and Table 3
if certain rules arre met,then I'd insert the record into Table2 and if not
set a flag on the record itself in Table1 and move to the next record(for
instance the record wasn't inserted)
How can I do that?
Thanks"ALI-R" <Ray@.Alirezaei.com> wrote in message
news:eTKOlwgHFHA.3936@.TK2MSFTNGP10.phx.gbl...
> I'd like to check every single record in Table1 with Table2 and Table 3
> if certain rules arre met,then I'd insert the record into Table2 and if
not
> set a flag on the record itself in Table1 and move to the next record(for
> instance the record wasn't inserted)
What rules? What flag? Can you post an example of what you actually
want to do?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Sorry I should give you a better information on Table1:
Table1:
ClientID StudentID Name Checked? Inserted? Updated?
---
1 22 John 0 0 0
2 23 Chen 0 0 0
3 34 Ted 0 0 0
4 22 Bod 0 0 0
,ok now here is a one of the rules:
I'd like to check if ClientID of each record exist in Table3 or not,if yes
is the StudentId different with the current record in Table1 or not ,if they
are the same ,I should ignore the record and move to the next record ,if
they are not the same I should insert the current record(in Table1) into
Table 2 and set the following flags in the current record
checked=1,Inserted=1.
So I make the comparisons with Table3 but I insert the record into Table2.
Thanks
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eB5bo1gHFHA.3612@.TK2MSFTNGP09.phx.gbl...
> "ALI-R" <Ray@.Alirezaei.com> wrote in message
> news:eTKOlwgHFHA.3936@.TK2MSFTNGP10.phx.gbl...
> not
> What rules? What flag? Can you post an example of what you actually
> want to do?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||Check whether this works for you
Insert Into Table2 Select T1.*,1,1 from Table1 T1, Table3 T3 where
T1.ClientId=T3.ClientId and T1.StudentId<>T3.StudentId
Madhivanan|||How about updating the flags in Table1?
Thanks for your help
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1109655494.932982.133240@.o13g2000cwo.googlegroups.com...
> Check whether this works for you
> Insert Into Table2 Select T1.*,1,1 from Table1 T1, Table3 T3 where
> T1.ClientId=T3.ClientId and T1.StudentId<>T3.StudentId
> Madhivanan
>|||What do you want to update on Table1?
Madhivanan|||Hi,
How about an "instead of" trigger on the table itself.
/Tobbe
create trigger EnforceRule on table2
instead of insert
as
--Rules for table1
if exists
(
select *
from table1 t1
join inserted i
on i.clientid = t1.clientid
and i.studentid = t1.studentid
)
begin
update t1
set t1.checked = 1
from table1 t1
join inserted i
on i.clientid = t1.clientid
and i.studentid = t1.studentid
end
--Rules for table3
else if exists
(
select *
from table3 t3
join inserted i
on i.internalid = t3.internalid
)
begin
..
end
--If no rules apply
else begin
insert table1
select *
from inserted
end|||oops, correction on the last statement ...
insert table2
select *
from inserted
"Tobbe" wrote:
> Hi,
> How about an "instead of" trigger on the table itself.
> /Tobbe
> create trigger EnforceRule on table2
> instead of insert
> as
> --Rules for table1
> if exists
> (
> select *
> from table1 t1
> join inserted i
> on i.clientid = t1.clientid
> and i.studentid = t1.studentid
> )
> begin
> update t1
> set t1.checked = 1
> from table1 t1
> join inserted i
> on i.clientid = t1.clientid
> and i.studentid = t1.studentid
> end
> --Rules for table3
> else if exists
> (
> select *
> from table3 t3
> join inserted i
> on i.internalid = t3.internalid
> )
> begin
> ...
> end
> --If no rules apply
> else begin
> insert table1
> select *
> from inserted
> end
>|||There are some flags in Table1 that need to be updated ,,for instance if any
record from Table1 is inserted into Table2 ,the equivalent flag needs to be
set,or if it's ignored same thing must happen
thanks
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1109662199.214901.129150@.l41g2000cwc.googlegroups.com...
> What do you want to update on Table1?
> Madhivanan
>|||Have you ever used this? How is the performance?
Thanks for the reply
"Tobbe" <Tobbe@.discussions.microsoft.com> wrote in message
news:70F8A13B-A8F9-49CE-BBE2-C475156E48DF@.microsoft.com...
> oops, correction on the last statement ...
> insert table2
> select *
> from inserted
>
> "Tobbe" wrote:
>
Showing posts with label studentid. Show all posts
Showing posts with label studentid. Show all posts
Monday, March 26, 2012
Query problem
Labels:
bodprimary,
chen3,
database,
followstable1clientid,
john2,
keyclientid,
microsoft,
mysql,
name-1,
oracle,
query,
server,
sql,
studentid,
studentidtable2internalid,
tables,
ted4
Friday, March 9, 2012
Query optimization
I have a staging table with the following fields:
LineNumber,StudentId,EnteranceYear,Statu
s and some other fields.
There are some duplicates based on two fields (studentID and EnteranceYear)
for which I'm trying to update the older record to be active (status=1) and
all the other duplicates to be inactive by setting status=0 .I wrote this
query first
UPDATE dbo.IF_C4TRANSFORM SET
Status = 0
WHERE FileID=2565 AND
LINE_LineNumber not in
(
select max(t.LineNumber)
from MyTable t
group by t.StudentId_ID , t.EnteranceYear
having count(*) > 1
)
but it dosen't work properly.if subquery return nothing in set the status of
all records to 0 ,no matter they are duplicated or not .then I created the
following query which I don;t like at all.
I'm just wondering if there is a better way to do so:
update MyTable t set
Status = 0
WHERE FILEID=2565 and
LINE_NUMBER IN
(
SELECT t.LineNumber FROM MyTable t
WHERE FileID=2565 and
Exists
(
SELECT 1
FROM MyTable t2
WHERE t.StudentId_ID = t2.StudentId_ID AND
t.EnteranceYear = t2.EnteranceYear AND
t.LineNumber< t2.LineNumber
)
)
Thanks a lotUPDATE MyTable
SET Status = 0
WHERE FileID=2565
AND NOT EXISTS
(select * from MyTable as t
where t.StudentID = MyTable.StudentID
and t.EntranceYear = MyTable.EntranceYear
and t.LineNumber > MyTable.LineNumber)
Roy
LineNumber,StudentId,EnteranceYear,Statu
s and some other fields.
There are some duplicates based on two fields (studentID and EnteranceYear)
for which I'm trying to update the older record to be active (status=1) and
all the other duplicates to be inactive by setting status=0 .I wrote this
query first
UPDATE dbo.IF_C4TRANSFORM SET
Status = 0
WHERE FileID=2565 AND
LINE_LineNumber not in
(
select max(t.LineNumber)
from MyTable t
group by t.StudentId_ID , t.EnteranceYear
having count(*) > 1
)
but it dosen't work properly.if subquery return nothing in set the status of
all records to 0 ,no matter they are duplicated or not .then I created the
following query which I don;t like at all.
I'm just wondering if there is a better way to do so:
update MyTable t set
Status = 0
WHERE FILEID=2565 and
LINE_NUMBER IN
(
SELECT t.LineNumber FROM MyTable t
WHERE FileID=2565 and
Exists
(
SELECT 1
FROM MyTable t2
WHERE t.StudentId_ID = t2.StudentId_ID AND
t.EnteranceYear = t2.EnteranceYear AND
t.LineNumber< t2.LineNumber
)
)
Thanks a lotUPDATE MyTable
SET Status = 0
WHERE FileID=2565
AND NOT EXISTS
(select * from MyTable as t
where t.StudentID = MyTable.StudentID
and t.EntranceYear = MyTable.EntranceYear
and t.LineNumber > MyTable.LineNumber)
Roy
Labels:
based,
database,
duplicates,
enteranceyear,
fields,
following,
linenumber,
microsoft,
mysql,
optimization,
oracle,
query,
server,
sql,
staging,
status,
studentid,
table
Wednesday, March 7, 2012
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.
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.
Subscribe to:
Posts (Atom)