Bobby
asked on
Access 2003 query, long list of "or" not working
I'm trying to query a table to not give me results for the following:
Not Like "USA" Or "US" Or "United States" Or "Untied States" Or "United States of America" Or "United States Minor Outlying Isl" Or "Canada" Or "CA" Or Is Null
I do not want the results from all the quotated ones, and I do want the null results. This returns all results and looks like it doesn't even factor in my conditions. What am I doing wrong?
Not Like "USA" Or "US" Or "United States" Or "Untied States" Or "United States of America" Or "United States Minor Outlying Isl" Or "Canada" Or "CA" Or Is Null
I do not want the results from all the quotated ones, and I do want the null results. This returns all results and looks like it doesn't even factor in my conditions. What am I doing wrong?
ASKER
Customers.Country
That is the table and field, and I need to see CustomerID and Country in the results.
How do I take your input and make a query with that? Are you saying use SQL view?
That is the table and field, and I need to see CustomerID and Country in the results.
How do I take your input and make a query with that? Are you saying use SQL view?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
whoo hoo! Thanks.
You are welcome.
BTW, take a look at https://www.experts-exchange.com/articles/18366/Subqueries-at-a-glance.html
for more on queries. Some of them are advance but soon or later you will need them all.
Mike
BTW, take a look at https://www.experts-exchange.com/articles/18366/Subqueries-at-a-glance.html
for more on queries. Some of them are advance but soon or later you will need them all.
Mike
ASKER
Will bookmark, thanks much.
You might also want to consider:
WHERE NZ([Country], "") NOT IN ("USA", "US", "United States", "United States of America", "United States Minor Outlying Isl", "Canada", "CA")
WHERE NZ([Country], "") NOT IN ("USA", "US", "United States", "United States of America", "United States Minor Outlying Isl", "Canada", "CA")
ASKER
Yeah, not in list seems much cleaner and easier to maintain.
Yes, it is better solution. With focus on And Or issue, I didn't want to try other things. I do not mind if you get this question opened to regrade or accept the good solution from Dale.
Mike
Mike
ASKER
Dale, would you like me to do that or do you just want me to get you next time?
just get me next time. I knew you had accepted Mike's answer, just wanted to present you with another option.
One of the down sides of using the IN ( ) syntax is that they seem to take longer to run than the equivalent
<> "A" AND <> "B" AND ...
syntax. A lot of times. I'll build a table of words to exclude from a query, then use a left join from the main table to that table, something like:
SELECT T.*
FROM myTable as T
Left Join tbl_ExcludeWorks as E ON T.[Country] = E.Exclude
WHERE E.ID IS NULL
This syntax runs quicker, but may not be updateable depending on you keys and unique indices. So if you need undateability, you would have to test it first.
One of the down sides of using the IN ( ) syntax is that they seem to take longer to run than the equivalent
<> "A" AND <> "B" AND ...
syntax. A lot of times. I'll build a table of words to exclude from a query, then use a left join from the main table to that table, something like:
SELECT T.*
FROM myTable as T
Left Join tbl_ExcludeWorks as E ON T.[Country] = E.Exclude
WHERE E.ID IS NULL
This syntax runs quicker, but may not be updateable depending on you keys and unique indices. So if you need undateability, you would have to test it first.
ASKER
Very good info. Thanks much.
@Dale,
I wonder if we can remove NZ() from left side. You have it like:
WHERE NZ([Country],"") NOT IN ("USA", "US", "United States", "United States of America", "United States Minor Outlying Isl", "Canada", "CA")
It will be faster doing like:
WHERE [Country] NOT IN ("USA", "US", "United States", "United States of America", "United States Minor Outlying Isl", "Canada", "CA") AND [Country] Is Not Null
The reason is that use of function on the left side is know as nonsargable query and thus very slow if the searched fields are indexed.
"Generally when we include a Function/operation in a column included in WHERE clause the query became Non-Sargable. ..." for more see: http://www.codeproject.com/Articles/827764/Sargable-query-in-SQL-server
I wonder if we can remove NZ() from left side. You have it like:
WHERE NZ([Country],"") NOT IN ("USA", "US", "United States", "United States of America", "United States Minor Outlying Isl", "Canada", "CA")
It will be faster doing like:
WHERE [Country] NOT IN ("USA", "US", "United States", "United States of America", "United States Minor Outlying Isl", "Canada", "CA") AND [Country] Is Not Null
The reason is that use of function on the left side is know as nonsargable query and thus very slow if the searched fields are indexed.
"Generally when we include a Function/operation in a column included in WHERE clause the query became Non-Sargable. ..." for more see: http://www.codeproject.com/Articles/827764/Sargable-query-in-SQL-server
add And [Country] <> Null later after testing the rest.