need result from two queries - 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

SQL Server 2000 Forums

SQL Server Development (2000)

need result from two queries

Author

Topic

JoshBeagley
Starting Member

5 Posts

Posted - 2005-05-03 : 02:19:27

Hello all,

I've jumped head first into SQL/ASP from having a few years of experience with MSAccess, and what I'm trying to do now is something I haven't tried even with Access:

I have a table of evaluation forms that an instructor writes on each student. I want the instructor to view the LATEST evaluation form that he wrote on each student. I've only managed to come up with this:

QUERY 1 - all the fields that I want, but too many records

strSQL = "SELECT * FROM TEvaluations WHERE instructor = 'Jones'"

QUERY 2 - all the records that I want, but I'm limited to only a few fields because of the way GROUP BY works

strSQL = "SELECT MAX(end_date) AS maxend_date, student, instructor FROM TEvaluations WHERE instructor = 'Jones' GROUP BY student, instructor"

Again what I'm looking for is the LAST record that an instructor wrote on each one of his students. A quick fix I discovered is to just add another field to each eval: latesteval = yes or no Then I could just add that to WHERE, but that means each time someone writes a new evaluation, the old one would have to somehow be "made old." Any thoughts are much appreciated! thanks

Josh

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-03 : 03:26:50

You could use a self join using a derived table.
Something like


SELECT T1.*
FROM TEvaluations T1 INNER JOIN 
			(SELECT MAX(end_date) AS maxend_date, student, instructor 
				FROM TEvaluations 
				GROUP BY student, instructor) T2
	ON T1.student = T2.student
		AND T1.instructor = T2.instructor 
		AND T1.end_date = T2.maxend_date
WHERE T1.instructor = 'Jones'

Andy

Beauty is in the eyes of the beerholder

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-03 : 03:33:22

On another note instead of coding SQL into your ASP, why dont you use a sproc instead and call that. This will also increase the performance of your query.

Andy

Beauty is in the eyes of the beerholder

JoshBeagley
Starting Member

5 Posts

Posted - 2005-05-03 : 10:50:39

Andy, I've been working on this for going on 2 weeks now and you finally did it. 1000 points for you! Thank you! But for future reference what is a sproc? And can I pull data from a stored query within Access instead of a table? I can see how I would confuse myself doing that, but I'm just curious. thanks again!

Josh

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-03 : 21:43:41

Josh
A sproc is a stored procedure, which generaly cant be used in access. unless its a pass through query or VBA coded as a recordset. (blah de blah)

Get a decent book (look on here) and read up
Sorry i cant be of any more help but i'm shit faced. ISTANBUL here i come!!!!!!!!!!!!!!!!

Andy

Beauty is in the eyes of the beerholder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources