Solved

SQL NOT IN clause rewrite with Parameters help

Posted on 2010-09-17
6
321 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now