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
Solved

SQL NOT IN clause rewrite with Parameters help

Posted on 2010-09-17
6
325 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

840 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