I'm not sure if I'm going to have to do in a couple queries or not, but assistance is appreciated.

STUDENTS
----------------
StudentID PK
FirstName
LastName

QUIZPAIRINGS  (Intersection)
---------------------
STUDENTID1 PK
STUDENTID2 PK
TESTID PK
bInRegularTest
bInAdvancedTest

SCORES
-------------
StudentID PK
TestID PK
Score


The object is to show the combined scores for the paired students who took the advanced test for test 101 sorted from highest to lowest combined score.

    Partner1, Partner 2, Partner 1 score, Partner 2 score, combined score
1)  John Doe | Mary Doe | 400 | 399 | 799
2)  Jenny Doe | Helen Doe | 300 | 340 | 740
3)  Martin Doe | Greg Doe | 200 | 220 | 420

So far, I have the following;

SELECT
   st1.firstname,
   st1.lastname,
   st2.firstname,
   st2.lastname,
   sc2.score,
   sc1.score,
   p.studentid1,
   p.studentid2
FROM
   student st1
INNER JOIN
   scores sc1
ON
   (
       st1.studentid = sc1.studentid)
INNER JOIN
   pairing p
ON
   (
       sc1.studentid = p.studentid1)
INNER JOIN
   scores sc2
ON
   (
       p.studentid2 = sc2.studentid)
INNER JOIN
   student st2
ON
   (
       sc2.studentid = st2.studentid)
WHERE
   p.testid = 101 ;

I think I have it.  Welcome any comments for improvement.

SELECT
   ST2.firstname,
   ST2.lastname,
   ST1.firstname,
   ST1.lastname,
   SC2.score,
   SC1.score,
   SC1.Score+SC2.score AS Total
FROM
   QUIZPAIRINGS QP
INNER JOIN
   SCORES SC1
ON
   (
       QP.studentid1 = SC1.studentid)
INNER JOIN
   STUDENTS ST1
ON
   (
       SC1.studentid = ST1.studentid)
INNER JOIN
   SCORES SC2
ON
   (
       QP.studentid2 = SC2.studentid)
INNER JOIN
   STUDENTS ST2
ON
   (
       SC2.studentid = ST2.studentid)
WHERE
   QP.testid = 101
ORDER BY
   Total DESC ;

Lance


The sql looks about as good as it will get, however, the criteria "who took the advanced test for test 101" doesn't seem to be tested for. Without knowing the numbering system should the WHERE clause be

WHERE
   QP.testid = 101
AND
bInAdvancedTest -- I assume its a boolean

Just in case you have't thought about it - its a good idea to run in EDBManager and ask for the execution plan and see if you need to add any indices.

Roy Lambert