Solved

SQL NOT IN clause rewrite with Parameters help

Posted on 2010-09-17
6
327 Views
Last Modified: 2012-05-10
Hi...  I am trying to rewrite a query and need your help.  Essentially, this is the query:

SELECT FirstName, LastName
FROM nameTable nt
JOIN userTable ut ON (ut.userId = nt.userId)
WHERE ut.userId NOT IN (SELECT userId FROM badUsers WHERE denyUser = ?)

I need to rewrite the query to allow for Parameterized SQL in ASP.  Currently the above query gives an error if I try to pass it a parameter (Parameters cannot be used in statements with a sub-query).  I would like to have it rewritten as maybe a JOIN such that it accepts a parameter and produces the same results.

The goal of the query is to make sure the users are NOT in the subset of bad users that have denyUser = TRUE or FALSE.  Obviously this is not the actual query and yes, I did make this up on the fly.  The original query is dynamically built based on the information passed to the script so it's difficult to supply an actual example due to it's dynamic nature.
0
Comment
Question by:goducks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 7

Expert Comment

by:tlovie
ID: 33704001
So if I understand this correctly, denyUser can either take the value of TRUE or FALSE... and you want the opposite of that?

I'm sort of confused by your question, but this is my best guess at this point....

SELECT FirstName, LastName
FROM nameTable nt
JOIN userTable ut ON (ut.userId = nt.userId)
inner join badUsers b on ut.UserID=b.UserID
WHERE  NOT denyUser = ?

follow up questions.... Are all users listed in badUsers table? or should we be left joining to that?
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33704077
Did you try using a stored procedure with input parameter Something like below..

You can call the stored proc and pass a parameter Like

Execute ProcName 'BaduserName'


Create Proc ProcName
@Baduser Varchar(100)

AS
BEGIN

SELECT FirstName, LastName
FROM nameTable nt
JOIN userTable ut ON (ut.userId = nt.userId)
WHERE ut.userId NOT IN (SELECT userId FROM badUsers WHERE denyUser = @Baduser) 

END

Open in new window

0
 
LVL 8

Expert Comment

by:ragnarok89
ID: 33704104
WHERE b.UserID IS NULL;

Al
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 2

Author Comment

by:goducks
ID: 33704116
Yes, either true or false can be passed in.  In fact, that particular value won't be true or false it will be an integer (represents a row ID of a different table, but that's nearly irrelevant - just telling you for context that it's not necessarily a true/false)

Not all users are in the badUsers table.

The point of the original query is that the subselect returns some sort of unbounded subset of numbers.  I want my query to choose everything that's NOT in that subset of numbers.  It's a simple thing, but it doesn't look like I can simplify it other than using a "NOT IN" operation.  I'm thinking there's a way to do it with JOIN, but my SQL is rusty and I'm not that good at conversion to equivalents.

If I use your example, we would get only values that are in badUsers AND userTable, right?  If so, I want only what's NOT in the badUsers table, but only those values in badUsers that have a "denyUser" value of a given integer.  That goes somewhat against what I was asking about denyUser in the original example, but let's stick to an integer for that field instead.
0
 
LVL 2

Author Comment

by:goducks
ID: 33704129
@vdr1620 - Can't use stored procedures.

@ragnarok89 - b.userId might be null (meaning that user isn't in the table), but it also might be an integer (sorry about that).  I want all values that don't match the integer I gave in the parameter.
0
 
LVL 2

Accepted Solution

by:
goducks earned 0 total points
ID: 33705009
Ok... haven't heard anything so I rewrote it to first get a list of the numbers I didn't want, then replaced the sub-select with the sub-set of those numbers.  Thanks for trying guys!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question