Solved

SQL NOT IN clause rewrite with Parameters help

Posted on 2010-09-17
6
324 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
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 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