WHERE NOT IN(...) not picking up value.

Question

WHERE NOT IN(...) not picking up value.

simflex-897410

Hall of Fame

Points: 3881
More actions
June 1, 2010 at 8:06 am

#233160

Hello again, Experts,
This code below is working great for me when used with MS Access database.
However, it doesn't work when used with SQL Server db.
Basically, a judge is assigned one case and one case only.
If the judge already has a case assigned to him/her, the case and the judge is assigned to disappears from the list.
So far, when I run this same code I have used with Access as backend which has worked beautifully, just can't find the client in the CASES table.
Please see a series of code snips with comments.
' now, does this client have any cases assigned to any Judge?
SELECT TOP 1 JudgeCode FROM Cases WHERE clientID = paramClientid AND Coalesce(JudgeCode,0) <> 0
--If the client has any cases assigned to a judge, update the CASES table with judgeCode
--Otherwise, this is where I am having problems
' if here, this is first case for this client...so find an available judge:
SELECT JudgeCode, JudgeName FROM Judges
WHERE JudgeCode NOT IN (SELECT JudgeCode FROM Cases)
ORDER BY JudgeName
This code works great with an Access DB but for some reason it just keeps saying no judges availabe.
If I remove this line:
WHERE JudgeCode NOT IN (SELECT JudgeCode FROM Cases)
then it finds judges but the problem is that it no longer enforces the ONLY one case per judge rule.
Could that code be written any other way?
I know this seems trivial but it isn't working for me.
Thanks as always for the kindness and continued assistance I have been receiving here.
June 1, 2010 at 8:18 am

This was removed by the editor as SPAM
June 1, 2010 at 8:24 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

simflex-897410 (6/1/2010)
' if here, this is first case for this client...so find an available judge:
SELECT JudgeCode, JudgeName FROM Judges
WHERE JudgeCode NOT IN (SELECT JudgeCode FROM Cases)
ORDER BY JudgeName
This code works great with an Access DB but for some reason it just keeps saying no judges availabe.
If I remove this line:
WHERE JudgeCode NOT IN (SELECT JudgeCode FROM Cases)

<<it just keeps saying no judges availabe>> this suggests that you are using the client to view the results of the sql code you're running against SQL Server. Have you checked the results in an SSMS or QA window? The statement looks just fine, and yes there are a few ways of writing exactly the same query. This is the most obvious:

SELECT j.JudgeCode, j.JudgeName

FROM Judges j

LEFT JOIN Cases c ON c.JudgeCode = j.JudgeCode

WHERE c.JudgeCode IS NULL

ORDER BY j.JudgeName

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 2

Thank you very, very much.

That worked like a dream.

Again, thanks a lot.