Joins nightmare - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joins nightmare

Author

Topic

templar-fenix
Starting Member

3 Posts

Posted - 2007-12-05 : 20:11:49

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

29 Posts

Posted - 2007-12-06 : 10:51:28

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

templar-fenix
Starting Member

3 Posts

Posted - 2007-12-07 : 07:41:36

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

templar-fenix
Starting Member

3 Posts

Posted - 2007-12-07 : 10:22:58

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources