Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

SQL NOT IN clause rewrite with Parameters help

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
goducks
Asked:
goducks
1 Solution
 
tlovieCommented:
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
 
vdr1620Commented:
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
 
ragnarok89Commented:
WHERE b.UserID IS NULL;

Al
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
goducksAuthor Commented:
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
 
goducksAuthor Commented:
@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
 
goducksAuthor Commented:
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now