sql server query help needed?

[mns76]

okay, I need to explain what I am trying to do.

I have a table that holds abstracts written for other ppl to judge. Each judge can login and see each abstract, and start judging.

My problem is that I am having a difficult time generating a query that will allow each judge to see all abstracts they judged, and all abstracts they have not judged. I know I need two separate recordsets for this, but the setup is stumping me.

So far, I have the following tables created:

abstracts - fields: abstractID, abstracttext
judges - fields: judgeID, password
judgedAbstracts - fields: judgeID, abstractID, score


Here is the sql statement I have come up with:

SELECT judgedAbstracts.judgeID, abstracts.abstractID, judgedAbstracts.abstractID
FROM abstracts INNER JOIN judgedAbstracts ON abstracts.abstractID = judgedAbstracts.abstractID
WHERE (((judgedAbstracts.judgeID)=1));


This gets me what is in the judgedAbstracts, but I want to show which abstracts are not yet judged - based on what IS judged from the judgedAbstracts table. As soon as a judge creates a score for an abstract and submits it into the judgedAbstracts table, I need each judge to know which abstracts are left to judge, while being able to see which abstracts are already judged.

anybody have any clues on how that is written?

thanks!

 

[lespaul]

This should work for you:

SELECT judgedAbstracts.judgeID, abstracts.abstractID, judgedAbstracts.abstractID
FROM abstracts LEFT JOIN judgedAbstracts ON abstracts.abstractID = judgedAbstracts.abstractID
WHERE (((judgedAbstracts.judgeID)=1));

I changed the INNER JOIN to a LEFT JOIN. That will bring all the records from judgedAbstracts and only the records that match from abstracts.

Leslie

 

[lespaul]

other way around, it will bring all the records from abstracts and only the matches from judgedabstratcts.


Leslie

 

[PHV]

Have you tried something like this ?
SELECT judgedAbstracts.judgeID, abstracts.abstractID, judgedAbstracts.abstractID
FROM judgedAbstracts RIGHT JOIN Abstracts ON judgedAbstracts.abstractID = Abstracts.abstractID
WHERE judgedAbstracts.judgeID=1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244