[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

"Not in" Subquery with WildCards...

I have two tables - the first is a list of projects with Project Numbers in the 'PROJ' field like this:

TE47000
TE47300
BD40999
BD43999
FI50000
FI49999

etc etc

I want a query to filter this list of projects bby wildcards eg:
Not Like "FI*"

However I want to hold the list of wildcards in a seperate table (there are going to be multiple queries which need updating regularly, so I dont want to have to edit each query - I'd rather hold the list of wildcards as a separate table.

So I've tried doing this as a subquery like this:

SELECT DISTINCT PROJECTTABLE.PROJ
FROM PROJECTTABLE
WHERE (((PROJECTTABLE.PROJ) Not In (Select [FILTERTABLE].[WILDCARD] from [FILTERTABLE])))

This doesnt work as the original project numbers dont match the filters, so it returns all projects..  What I need to do is make if 'Not Like' rather than 'Not In' but this returns an error.

Any ideas?..
0
xTheBanditx
Asked:
xTheBanditx
  • 2
  • 2
1 Solution
 
MargarePCommented:
Maybe <> ?
0
 
NGPSoft1Commented:
It's a little crude, but you could use something like

SELECT DISTINCT PROJECTTABLE.PROJ
FROM PROJECTTABLE
WHERE Left(PROJECTTABLE.PROJ,2)<>"FI"
0
 
xTheBanditxAuthor Commented:
This isnt the solution I need... I need the list of 'FI' and other types to sit in a seperate table and compare the two, rather than edit these in the query itself..

Any0one else?
0
 
NGPSoft1Commented:
What about this:

SELECT DISTINCT PROJECTTABLE.PROJ
FROM PROJECTTABLE
WHERE Left(PROJECTTABLE.PROJ,2) Not In (Select [FILTERTABLE].[WILDCARD] from [FILTERTABLE])

This is of course assuming the FilterTable.Wildcard data is all 2 characters. If it's not, then:
SELECT DISTINCT PROJECTTABLE.PROJ
FROM PROJECTTABLE
WHERE Left(PROJECTTABLE.PROJ,2) Not In (Select Left([FILTERTABLE].[WILDCARD],2) from [FILTERTABLE])


One additional note: Not In subqueries tend to run pretty slow, especially on a big list.
0
 
xTheBanditxAuthor Commented:
thats the puppy.... much obliged... ;)

nick
0

Featured Post

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!

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