"L.Peter" <peter@local.local> wrote in message
news:uN3mLHA2GHA.4312@TK2MSFTNGP02.phx.gbl...
> Hi Group,
> I have been struggling this case for two days but still could not
proceduce
> the desired result.
> I have
> student table:
> studentid studentname
> 001        student 1
> 002        student 2
> 003        student 3
>
> subject:
> subid      name  presub  postsub
> 02en       english
> 03en       english  02en       04en
> 04en       english  03en
> 04fr        french  03fr
> 03fr        french
>
> studentSubject
> id studentid subject  link(this links to studentClass) comment (ntext
data)
> 2 001        04en       1     NULL
> 3 002        03en       2     NULL
> 4 003        03en       3     NULL
> 5 002        04fr        4     NULL
>
> StudentClass
> id link subid  year teacher  Class
> 1 2 03en         2006 teach2     1
> 2 1 04en         2006 teach1     1
> 3 3 03en         2006 teach3     3
> 4 4 04fr  2006 teach4  2
>
> studentTest
> id student  grade testdate
> 1 002        8  2006-05-30 00:00:00.000
> 2 003        5  2006-03-28 00:00:00.000
> 3 002        4  2006-04-28 00:00:00.000
>
> Old_ST_Sub
> id student  subject  comments (ntext data)                  archive_date
> 1 002        02en       need to improve on 02en term 1 2006-02-28
> 00:00:00.000
> 2 002        02en       need to improve on 02en term 2 2006-05-28
> 00:00:00.000
> 3 001        03en       need to improve on 03en term 2 2006-05-28
> 00:00:00.000
> 4 002        04fr        need to improve on 04fr term 2   2006-05-28
> 00:00:00.000
>
> every term, we archive data from studentsubject to old_st_sub
> so I want to get student name, current subject (from student Subject),
> current teacher (from studentClass),
> latest test grade for that student based on testdate (studentTest), the
> latest comments (based on archive_date)
> from old_st_sub where old_st_sub.subject = subject.presub for the subject
> that the student is taking
>
> the expected result is
> student     currentsubject teacher      grade     lastComment
> 001         04en                  teach1                   need to improve
> on 03en term 2
> 002         03en                  teach2      8           need to improve
on
> 02en term 2
> 002         04fr                   teach4      8
> 003         03en                  teach3      5
>
> I only have select right, so the sql needs to be in one statement
>
> TIA
> Peter
>
>

"L.Peter" <peter@local.local> wrote in message
news:upgTcSG2GHA.4932@TK2MSFTNGP02.phx.gbl...
> Hi Group,
> Here is the ddl:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_studentSubject_Subject]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[studentSubject] DROP CONSTRAINT
> FK_studentSubject_Subject
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_studentSubject_student]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[studentSubject] DROP CONSTRAINT
> FK_studentSubject_student
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_studentClass_studentSubject]') and
> OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[studentClass] DROP CONSTRAINT
> FK_studentClass_studentSubject
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Old_ST_SUB]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[Old_ST_SUB]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Subject]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Subject]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Teacher]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Teacher]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[student]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[studentClass]') and OBJECTPROPERTY(id, N'IsUserTable')
> =
> 1)
> drop table [dbo].[studentClass]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[studentSubject]') and OBJECTPROPERTY(id,
> N'IsUserTable')
> = 1)
> drop table [dbo].[studentSubject]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[studenttest]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[studenttest]
> GO
>
> CREATE TABLE [dbo].[Old_ST_SUB] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [studentid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> [subid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> [comments] [ntext] COLLATE Latin1_General_CI_AS NULL ,
> [archdate] [datetime] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Subject] (
> [subid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> [name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [presub] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> [postsub] [char] (10) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Teacher] (
> [id] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> [name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[student] (
> [id] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> [name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[studentClass] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [link] [int] NOT NULL ,
> [subid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> [aca_year] [int] NOT NULL ,
> [teacherid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Class] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[studentSubject] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [studentid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> [subid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> [link] [int] NOT NULL ,
> [comment] [ntext] COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[studenttest] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [studentid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> [mark] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
> [testdate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> insert into student(id,name) values('001','student 1')
> insert into student(id,name) values('002','student 2')
> insert into student(id,name) values('003','student 3')
> go
> insert into subject(subid,name,presub,postsub)
> values('02en','english','','')
> insert into subject(subid,name,presub,postsub)
> values('03en','english','02en','04en')
> insert into subject(subid,name,presub,postsub)
> values('04en','english','03en','')
> insert into subject(subid,name,presub,postsub)
> values('04fr','french','03fr','')
> insert into subject(subid,name,presub,postsub)
> values('03fr','french','','')
> 04fr        french  03fr
> 03fr        french
> go
> insert into teacher(id,name) values('teach1','teacher 1')
> insert into teacher(id,name) values('teach2','teacher 2')
> insert into teacher(id,name) values('teach3','teacher 3')
> insert into teacher(id,name) values('teach4','teacher 4')
> go
> insert into studentSubject(studentid,subid,link) values('001','04en',1)
> insert into studentSubject(studentid,subid,link) values('002','03en',2)
> insert into studentSubject(studentid,subid,link) values('003','03en',3)
> insert into studentSubject(studentid,subid,link) values('002','04fr',4)
> go
> insert into studentclass(link,subid,aca_year,teacherid,class)
> values(2,'03en',2006,'teach2',1)
> insert into studentclass(link,subid,aca_year,teacherid,class)
> values(1,'04en',2006,'teach1',1)
> insert into studentclass(link,subid,aca_year,teacherid,class)
> values(3,'03en',2006,'teach3',3)
> insert into studentclass(link,subid,aca_year,teacherid,class)
> values(4,'04fr',2006,'teach4',4)
> go
> insert into studenttest(studentid,mark,testdate)
> values('002',8,'2006.05.30')
> insert into studenttest(studentid,mark,testdate)
> values('003',5,'2006.03.28')
> insert into studenttest(studentid,mark,testdate)
> values('002',4,'2006.04.28')
> go
> insert into Old_st_sub(studentid,subid,comments,archdate)
> values('002','02en','need to improve on 02en term 1','2005.02.28')
> insert into Old_st_sub(studentid,subid,comments,archdate)
> values('002','02en','need to improve on 02en term 2','2005.05.28')
> insert into Old_st_sub(studentid,subid,comments,archdate)
> values('001','03en','need to improve on 03en term 2','2005.05.28')
>
> i am using sql200
> the expected result is
>
> student-->currentsubject-->teacher-->grade-->lastComment
> 001         04en                    teach1                   need to
> improve
> on 03en term 2
> 002         03en                    teach2      8           need to
> improve
> on 02en term 2
> 002         04fr                     teach4      8
> 003         03en                    teach3      5
>
> Please help
> TIA
> Peter
>
>
> "L.Peter" <peter@local.local> wrote in message
> news:uN3mLHA2GHA.4312@TK2MSFTNGP02.phx.gbl...
>> Hi Group,
>> I have been struggling this case for two days but still could not
> proceduce
>> the desired result.
>> I have
>> student table:
>> studentid studentname
>> 001        student 1
>> 002        student 2
>> 003        student 3
>>
>> subject:
>> subid      name  presub  postsub
>> 02en       english
>> 03en       english  02en       04en
>> 04en       english  03en
>> 04fr        french  03fr
>> 03fr        french
>>
>> studentSubject
>> id studentid subject  link(this links to studentClass) comment (ntext
> data)
>> 2 001        04en       1     NULL
>> 3 002        03en       2     NULL
>> 4 003        03en       3     NULL
>> 5 002        04fr        4     NULL
>>
>> StudentClass
>> id link subid  year teacher  Class
>> 1 2 03en         2006 teach2     1
>> 2 1 04en         2006 teach1     1
>> 3 3 03en         2006 teach3     3
>> 4 4 04fr  2006 teach4  2
>>
>> studentTest
>> id student  grade testdate
>> 1 002        8  2006-05-30 00:00:00.000
>> 2 003        5  2006-03-28 00:00:00.000
>> 3 002        4  2006-04-28 00:00:00.000
>>
>> Old_ST_Sub
>> id student  subject  comments (ntext data)                  archive_date
>> 1 002        02en       need to improve on 02en term 1 2006-02-28
>> 00:00:00.000
>> 2 002        02en       need to improve on 02en term 2 2006-05-28
>> 00:00:00.000
>> 3 001        03en       need to improve on 03en term 2 2006-05-28
>> 00:00:00.000
>> 4 002        04fr        need to improve on 04fr term 2   2006-05-28
>> 00:00:00.000
>>
>> every term, we archive data from studentsubject to old_st_sub
>> so I want to get student name, current subject (from student Subject),
>> current teacher (from studentClass),
>> latest test grade for that student based on testdate (studentTest), the
>> latest comments (based on archive_date)
>> from old_st_sub where old_st_sub.subject = subject.presub for the subject
>> that the student is taking
>>
>> the expected result is
>> student     currentsubject teacher      grade     lastComment
>> 001         04en                  teach1                   need to
>> improve
>> on 03en term 2
>> 002         03en                  teach2      8           need to improve
> on
>> 02en term 2
>> 002         04fr                   teach4      8
>> 003         03en                  teach3      5
>>
>> I only have select right, so the sql needs to be in one statement
>>
>> TIA
>> Peter
>>
>>
>
>

"Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
news:RuCdndR7A4B5jpfYnZ2dnUVZ_rmdnZ2d@comcast.com...
> Hi Peter,
> Thanks for providing the DDL, try:
>
> Select x.studentid As student,
>   x.subid As currentsubject,
>   x.teacherid As teacher,
>   x.grade,
>   Coalesce(os.comments, '') As lastComment
> From (Select ssub.studentid,
>   ssub.subid,
>   sc.teacherid,
>   (Select Coalesce(Max(st1.mark), '') From studenttest st1
>      Where st.studentid = st1.studentid
>       And st.testdate = st1.testdate) As grade,
>   os.archdate,
>   s.presub
>  From subject s
>  Inner Join studentsubject ssub On s.subid = ssub.subid
>  Inner Join studentclass sc On ssub.link = sc.link
>  Left Join (Select st.studentid, Max(st.testdate) As testdate
>   From studenttest st
>   Group By st.studentid) st On ssub.studentid = st.studentid
>  Left Join (Select os.studentid, os.subid, Max(os.archdate) As archdate
>   From Old_st_sub os
>   Group By os.studentid, os.subid) os
>     On ssub.studentid = os.studentid
>       And s.presub = os.subid) x
>
> Left Join Old_st_sub os
>      On x.studentid = os.studentid
>      And x.presub = os.subid
>      And x.archdate = os.archdate
>
> Order By x.studentid, x.subid
>
> Tom
>
> "L.Peter" <peter@local.local> wrote in message
> news:upgTcSG2GHA.4932@TK2MSFTNGP02.phx.gbl...
> > Hi Group,
> > Here is the ddl:
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[FK_studentSubject_Subject]') and OBJECTPROPERTY(id,
> > N'IsForeignKey') = 1)
> > ALTER TABLE [dbo].[studentSubject] DROP CONSTRAINT
> > FK_studentSubject_Subject
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[FK_studentSubject_student]') and OBJECTPROPERTY(id,
> > N'IsForeignKey') = 1)
> > ALTER TABLE [dbo].[studentSubject] DROP CONSTRAINT
> > FK_studentSubject_student
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[FK_studentClass_studentSubject]') and
> > OBJECTPROPERTY(id,
> > N'IsForeignKey') = 1)
> > ALTER TABLE [dbo].[studentClass] DROP CONSTRAINT
> > FK_studentClass_studentSubject
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[Old_ST_SUB]') and OBJECTPROPERTY(id, N'IsUserTable')
=
> > 1)
> > drop table [dbo].[Old_ST_SUB]
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[Subject]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> > drop table [dbo].[Subject]
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[Teacher]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> > drop table [dbo].[Teacher]
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[student]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> > drop table [dbo].[student]
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[studentClass]') and OBJECTPROPERTY(id,
N'IsUserTable')
> > =
> > 1)
> > drop table [dbo].[studentClass]
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[studentSubject]') and OBJECTPROPERTY(id,
> > N'IsUserTable')
> > = 1)
> > drop table [dbo].[studentSubject]
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[studenttest]') and OBJECTPROPERTY(id, N'IsUserTable')
=
> > 1)
> > drop table [dbo].[studenttest]
> > GO
> >
> > CREATE TABLE [dbo].[Old_ST_SUB] (
> > [id] [int] IDENTITY (1, 1) NOT NULL ,
> > [studentid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> > [subid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> > [comments] [ntext] COLLATE Latin1_General_CI_AS NULL ,
> > [archdate] [datetime] NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> >
> > CREATE TABLE [dbo].[Subject] (
> > [subid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> > [name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> > [presub] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > [postsub] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE TABLE [dbo].[Teacher] (
> > [id] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> > [name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE TABLE [dbo].[student] (
> > [id] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> > [name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE TABLE [dbo].[studentClass] (
> > [id] [int] IDENTITY (1, 1) NOT NULL ,
> > [link] [int] NOT NULL ,
> > [subid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> > [aca_year] [int] NOT NULL ,
> > [teacherid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> > [Class] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE TABLE [dbo].[studentSubject] (
> > [id] [int] IDENTITY (1, 1) NOT NULL ,
> > [studentid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> > [subid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> > [link] [int] NOT NULL ,
> > [comment] [ntext] COLLATE Latin1_General_CI_AS NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> >
> > CREATE TABLE [dbo].[studenttest] (
> > [id] [int] IDENTITY (1, 1) NOT NULL ,
> > [studentid] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> > [mark] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
> > [testdate] [datetime] NOT NULL
> > ) ON [PRIMARY]
> > GO
> > insert into student(id,name) values('001','student 1')
> > insert into student(id,name) values('002','student 2')
> > insert into student(id,name) values('003','student 3')
> > go
> > insert into subject(subid,name,presub,postsub)
> > values('02en','english','','')
> > insert into subject(subid,name,presub,postsub)
> > values('03en','english','02en','04en')
> > insert into subject(subid,name,presub,postsub)
> > values('04en','english','03en','')
> > insert into subject(subid,name,presub,postsub)
> > values('04fr','french','03fr','')
> > insert into subject(subid,name,presub,postsub)
> > values('03fr','french','','')
> > 04fr        french  03fr
> > 03fr        french
> > go
> > insert into teacher(id,name) values('teach1','teacher 1')
> > insert into teacher(id,name) values('teach2','teacher 2')
> > insert into teacher(id,name) values('teach3','teacher 3')
> > insert into teacher(id,name) values('teach4','teacher 4')
> > go
> > insert into studentSubject(studentid,subid,link) values('001','04en',1)
> > insert into studentSubject(studentid,subid,link) values('002','03en',2)
> > insert into studentSubject(studentid,subid,link) values('003','03en',3)
> > insert into studentSubject(studentid,subid,link) values('002','04fr',4)
> > go
> > insert into studentclass(link,subid,aca_year,teacherid,class)
> > values(2,'03en',2006,'teach2',1)
> > insert into studentclass(link,subid,aca_year,teacherid,class)
> > values(1,'04en',2006,'teach1',1)
> > insert into studentclass(link,subid,aca_year,teacherid,class)
> > values(3,'03en',2006,'teach3',3)
> > insert into studentclass(link,subid,aca_year,teacherid,class)
> > values(4,'04fr',2006,'teach4',4)
> > go
> > insert into studenttest(studentid,mark,testdate)
> > values('002',8,'2006.05.30')
> > insert into studenttest(studentid,mark,testdate)
> > values('003',5,'2006.03.28')
> > insert into studenttest(studentid,mark,testdate)
> > values('002',4,'2006.04.28')
> > go
> > insert into Old_st_sub(studentid,subid,comments,archdate)
> > values('002','02en','need to improve on 02en term 1','2005.02.28')
> > insert into Old_st_sub(studentid,subid,comments,archdate)
> > values('002','02en','need to improve on 02en term 2','2005.05.28')
> > insert into Old_st_sub(studentid,subid,comments,archdate)
> > values('001','03en','need to improve on 03en term 2','2005.05.28')
> >
> > i am using sql200
> > the expected result is
> >
> > student-->currentsubject-->teacher-->grade-->lastComment
> > 001         04en                    teach1                   need to
> > improve
> > on 03en term 2
> > 002         03en                    teach2      8           need to
> > improve
> > on 02en term 2
> > 002         04fr                     teach4      8
> > 003         03en                    teach3      5
> >
> > Please help
> > TIA
> > Peter
> >
> >
> > "L.Peter" <peter@local.local> wrote in message
> > news:uN3mLHA2GHA.4312@TK2MSFTNGP02.phx.gbl...
> >> Hi Group,
> >> I have been struggling this case for two days but still could not
> > proceduce
> >> the desired result.
> >> I have
> >> student table:
> >> studentid studentname
> >> 001        student 1
> >> 002        student 2
> >> 003        student 3
> >>
> >> subject:
> >> subid      name  presub  postsub
> >> 02en       english
> >> 03en       english  02en       04en
> >> 04en       english  03en
> >> 04fr        french  03fr
> >> 03fr        french
> >>
> >> studentSubject
> >> id studentid subject  link(this links to studentClass) comment (ntext
> > data)
> >> 2 001        04en       1     NULL
> >> 3 002        03en       2     NULL
> >> 4 003        03en       3     NULL
> >> 5 002        04fr        4     NULL
> >>
> >> StudentClass
> >> id link subid  year teacher  Class
> >> 1 2 03en         2006 teach2     1
> >> 2 1 04en         2006 teach1     1
> >> 3 3 03en         2006 teach3     3
> >> 4 4 04fr  2006 teach4  2
> >>
> >> studentTest
> >> id student  grade testdate
> >> 1 002        8  2006-05-30 00:00:00.000
> >> 2 003        5  2006-03-28 00:00:00.000
> >> 3 002        4  2006-04-28 00:00:00.000
> >>
> >> Old_ST_Sub
> >> id student  subject  comments (ntext data)
archive_date
> >> 1 002        02en       need to improve on 02en term 1 2006-02-28
> >> 00:00:00.000
> >> 2 002        02en       need to improve on 02en term 2 2006-05-28
> >> 00:00:00.000
> >> 3 001        03en       need to improve on 03en term 2 2006-05-28
> >> 00:00:00.000
> >> 4 002        04fr        need to improve on 04fr term 2   2006-05-28
> >> 00:00:00.000
> >>
> >> every term, we archive data from studentsubject to old_st_sub
> >> so I want to get student name, current subject (from student Subject),
> >> current teacher (from studentClass),
> >> latest test grade for that student based on testdate (studentTest), the
> >> latest comments (based on archive_date)
> >> from old_st_sub where old_st_sub.subject = subject.presub for the
subject
> >> that the student is taking
> >>
> >> the expected result is
> >> student     currentsubject teacher      grade     lastComment
> >> 001         04en                  teach1                   need to
> >> improve
> >> on 03en term 2
> >> 002         03en                  teach2      8           need to
improve
> > on
> >> 02en term 2
> >> 002         04fr                   teach4      8
> >> 003         03en                  teach3      5
> >>
> >> I only have select right, so the sql needs to be in one statement
> >>
> >> TIA
> >> Peter
> >>
> >>
> >
> >
>
>