SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Joins nightmare
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

templar-fenix
Starting Member

3 Posts

Posted - 12/05/2007 :  20:11:49  Show Profile  Reply with Quote
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 lndStudent 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

jfuex
Starting Member

USA
29 Posts

Posted - 12/06/2007 :  10:51:28  Show Profile  Reply with Quote
How about something like this:

SELECT tblStudent.anStudentId,
tblStudent.txtFullName,
tblStudent.txtMobile,
tblClass.txtClassCode
(Select top 1 txtClassCode
FROM tblStudentClass
Inner join tblClass ON (anClassID=lngclassid)
WHERE (tblStudentClass.lngStudent=tblStudent.StudentID)
ORDER BY dtStartDate(datetime)) As LastClassCode

FROM From tblStudent
WHERE tblStudent.txtMobile LIKE '447_________'

Of course I don't have your original tables, so there might be some typos in here, but you get the gist. If the number of students is relatively small this should perform okay, but will get worse as the number of students increase. However it will give you the correct answer.

ooo
O

ooo
O
Go to Top of Page

templar-fenix
Starting Member

3 Posts

Posted - 12/07/2007 :  07:41:36  Show Profile  Reply with Quote
Wow that's great! Thank you very much. I've been struggling on this problem for months.

But now I have some records with NULL values in LastClassCode
I assume this is because there are some students in tblStudent table, but not in tblStudentClass, how can I filter out those?

Also table tblStudentClass now holds half a million records so performance is important. Do you think there is anything I could do to optimize this query except for rewriting table structures :)? Will stuff like temporary tables and stored procs help?

Thanks
Go to Top of Page

templar-fenix
Starting Member

3 Posts

Posted - 12/07/2007 :  10:22:58  Show Profile  Reply with Quote
Looks like I figured it out myself so the final query is

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 this query runs for 6 seconds now
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000