I have a query that I would like to exlude US States from. I am trying to find all data related to countries other than the US. I have the abbreviations for all the US States and can either link the table to the query and exlude them somehow or write a statement in the query to exlude them. But, I don't know how to do either. I thought I could do Not "'AK', 'IA', 'KY', 'WA'" but that isn't working. Any suggestions?
+1
A:
Can you do something like this:
SELECT * from table1 as t1
LEFT JOIN StatesTable as st
ON t1.State = st.State
WHERE st.id is null
You can also use NOT IN but i think that is a performance no-no. Example:
SELECT * FROM table1 where State NOT IN
(SELECT State from StatesTable)
Abe Miessler
2010-08-25 16:00:08
Aside from performance, a bigger problem with NOT IN may be that it would exclude rows where State is Null. But not a problem at all if that's what the OP actually wants. I would use your LEFT JOIN example.
HansUp
2010-08-25 17:30:18
With a set of records this small, I strongly doubt there's any performance penalty to the NOT IN equivalent. The reason NOT IN is to be avoided where possible is that the Jet/ACE query optimizer for some reason doesn't reliably use the indexes on both sides of the comparison. With as few records as are involved in a states table, I don't think this could possibly be a problem in the current instance. That said, I'd do it with a JOIN myself rather than a subquery. I would only use a subquery when I needed an editable result set and the JOIN made it uneditable.
David-W-Fenton
2010-08-25 20:16:39
A:
I can't recall exactly what SQL syntax MS-Access uses, but I think you can do something like
SELECT ... FROM ... WHERE StateAbbrev NOT IN ('AK', 'IA', 'KY', 'WA')If you had the state data in a table, you could remove it a couple of ways. Here is one:
SELECT ... FROM ... WHERE StateAbbrev NOT IN (SELECT Abbrev FROM States)There are lots of variations on this second method. This is one and it works, it trades readability for performance.
Aaron D
2010-08-25 16:01:40
A:
Does your states table include non-US State values, like Canadian provinces?
If so, add another boolean field to your states table named isInUS and mark as true the 50 states in the US.
Then check for rows whose related state value is not in the US.
Otherwise, if your states table contains 50 rows, use the left join Aaron posted.
Beth
2010-08-25 16:12:52
oops, sorry. meant Abe.SELECT * from table1 as t1LEFT JOIN StatesTable as st
Beth
2010-08-25 17:03:22