December 5, 2007 at 6:25 pm
Dear All
I have three tables
tblStudent
----------------------------------------------
|anStudentId|txtFullName|txtMobile|
----------------------------------------------
tblClass
--------------------------------
|anClassID|txtClassCode|
--------------------------------
tblStudentClass
-------------------------------------------------------------------------------------------------------
|id(int)|lngStudent(int)|lngClass(int)|dtStartDate(datetime)|dtEndDate(datetime)|
-------------------------------------------------------------------------------------------------------
Table tblStudentClass stores all information about classes that student was in, and will be in.
So there are plenty of records with the same lngStudent field.
What I need is to pull out information about all students from table tblStudent, and the last classes
the were/are in, or will be in if they are future students.
So the needed output should in this form with no duplicates
----------------------------------------------------------------
|anStudentId|txtFullName|txtMobile|txtClassCode|
----------------------------------------------------------------
What I have so far is this query:
SELECT tblStudent.anStudentId,tblStudent.txtFullName,tblStudent.txtMobile,tblClass.txtClassCode
FROM
(tblStudent
INNER JOIN
(
SELECT tblStudentClass.lngStudent, MAX(tblStudentClass.lngClass) AS LastOflngClass
FROM tblStudentClass
GROUP BY tblStudentClass.lngStudent
) AS s3
ON tblStudent.anStudentID = s3.lngStudent)
INNER JOIN tblClass
ON s3.LastOflngClass = tblClass.anClassID
WHERE
tblStudent.txtMobile LIKE '447_________'
ORDER BY tblStudent.txtMobile;
Which is not exactly what I want because it aggregates by greatest classid, not
by latest date, and it is slow on very large dataset. Maybe there are ways to optimize
it (temporary tables,stored procs,views)?
Many thanks in advance
December 6, 2007 at 3:54 am
Well, It would have been very easy if you were using SQL 2005, otherwise what you could do is...
1. Create a temporary table #VT with IDENTITY column
2. Insert data from tblStudentClass into #VT ordered by StudentID ASC, dtEndDate DESC
3. Use derived table to get StudentID with maximum of the IDENTITY value of #VT.
4. Join the result of step(3) to table #VT on IDENTITY columns
5. Join the result of step(4) to tables Student and Class on their related columns...
--Ramesh
December 7, 2007 at 5:49 am
Thank you for your quick reply! I will try this, but one quick question. Table tblStudentClass now holds half a million records, how will it affect performance if I am creating temporary table and moving all data from tblStudentClass to it?
December 7, 2007 at 6:09 am
I think you need to look at your table structure as a starting point. Your table with the student and class id have fields, which is the natural key (and would be the key except that students sometimes repeat the same course) have start end and dates which refer only to the class. These two fields should be in another table. Since the entry in that table could possibly only refer to one iteration of the class, you could then use a table to join that then did have the class id/student id as a key, and then try applying a cluster index to one or the other field to see if this speeds the result up.
December 7, 2007 at 8:42 am
Thanks everyone! I've got it working like this:
SELECT tblStudent.anStudentId,
tblStudent.txtFullName,
tblStudent.txtMobile,
(SELECT TOP 1 txtClassCode
FROM tblStudentClass
INNER JOIN tblClass ON (anClassID=lngClass)
WHERE (tblStudentClass.lngStudent=tblStudent.anStudentID)
ORDER BY dtLatestEndDate DESC) As LastClassCode
FROM tblStudent
WHERE
EXISTS (SELECT 1 FROM tblStudentClass WHERE tblStudentClass.lngStudent =tblStudent.anStudentID)
AND tblStudent.txtMobile LIKE '447_________'
Though it's slow (~6sec) but at least it gives the result I want
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply