473,327 Members | 2,118 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Help Request for an efficient alternative to "NOT EXISTS" or "NOT IN"

Hi, in MS Access, I am looking for an efficient way of returning records where field values in one table are NOT present when compared to another table. I can do this using NOT EXISTS or NOT IN but they take for ever to run.

I've been looking a LEFT and RIGHT JOINs but have destroyed too many brain cells in the process and am now totally confused !

I have two tables (that describe Bird Populations as it happens!).

The relevant fields in the main Bird Sightings table (Table 1) are SPECIES, SQUARE and TETRAD which describe very many bird species sightings and the 2k geograpical squares (Square and Tetrad) they were seen in.

Table two contains fields SQUARE, TETRAD and a FLAG that lists all possible SQUARES and TETRADs in and around the County where a Y in the flag field denotes the tetrad being wholly within the county boundary.

I'd like to be able to select SPECIES, SQUARE and TETRAD identifying those SQUAREs and TETRADs where the Species has no sightings (for FLAG "Y" Tetrads only).

Could anybody suggest the most efficient SQL to do this please?

Many thanks in anticipation
Phil Davis
Sep 11 '12 #1
17 1955
zmbd
5,501 Expert Mod 4TB
You're lacking a primary key in both tables.
Please confirm the relationship between the two tables would be on the fields [square] and [Tetrad]? There is no real relationship given... we can only guess.

What does Table_one store in the fields [square] and [Tetrad]?

What does Table_two store in the [square] and [Tetrad] fields?

-z
Sep 11 '12 #2
Rabbit
12,516 Expert Mod 8TB
If you mean you want to find all locations where there have been no sightings whatsoever, then a simple left/right outer join is all you need where the other side is null.

But if you mean a listing of locations where there have been no sightings by species, then you need a third species table, cross joined to the locations and then left joined to the sightings table where the sightings table is null.
Sep 11 '12 #3
Hi zmbd, many thanks for your reply. I guess you've deduced there is no primary key - your right! - by the time taken to process a NOT EXISTS or NOT IN clause?

Table 1 Square and Tetrad fields contain the Ordnance Survey 10k square id (eg SP00, SU05, ST69, SO50) and Tetrad id (a single character A-N or P-Z) that identifies a 2k square piece of ground within the UK.

Tables 2 contains the same content with all possible combinations (for Gloucesestershire) which is 683 2k squares wholly contained in Glos - marked with a "Y" flag - and hundreds of thousands of all sightings including thousands of sightings for certain commoner species (eg Blackbird).

I didn't assign a primary key to table1 because there is virtually no uniqueness in any field of table 1 but I guess I could make up a tetradid field (eg SP00A) in table 2 all of which would be unique. I'm giving away my lack of database design knowledge here probably!?
Sep 11 '12 #4
Hi Rabbit, many thanks for your reply also. Following on from my response to zmbd, my requirement is for the second of your scenarios (a listing of locations where there have been no sightings by species).

I follow the idea of the null field but after many syntax issues I gave up thinking it may have been an Access restriction.

Would you be able to describe what the third table would contain in terms of fields and an SQL model to show how it all hangs together please? My brain is fryed !!
Sep 11 '12 #5
Rabbit
12,516 Expert Mod 8TB
The third table would contain all distinct possible combinations of square and tetrad.

If you post the code you tried along with the error, I'd be able to help. But without seeing any of that, I wouldn't be able to tell you where the code went wrong.
Sep 11 '12 #6
zmbd
5,501 Expert Mod 4TB
Table two still seems to be your issue...

You need a way to tell me what species is being counted in table two.

Please, post us about the first three or four records from table
two.

Now let’s look at that square and tetrad...
Is the "square" a unique property of the area surveyed?
Is the "tetrad" a unique property of the "square"?
If so, then the [square]+[tetrad] is potentially a primary key.... that is... if I take "SU005" and "N" against a survey map, I should find that area once and only once.

-z
Sep 11 '12 #7
Hi Rabbit and zmbd, I'll try to answer both your posts in one.

Rabbit, Table 2 already contains all possible combinations of Square and Tetrad (within Gloucestershire) 683 of them. I have recreated the LEFT JOIN code I used and overcome the syntax error! However it now produces zero records where desired result would be one record containing:

BLACKBIRD ST69 J

Expand|Select|Wrap|Line Numbers
  1. SELECT [Atlas Field note book].Species, [Atlas Field note book].Square,[Atlas Field note book].Tetrad
  2.  
  3. FROM [Atlas Field note book] LEFT JOIN Glos_Tetrads ON (([Atlas Field note book].Square = Glos_Tetrads.Square) AND ([Atlas Field note book].Tetrad = Glos_Tetrads.Tetrad))
  4.  
  5. WHERE [Atlas Field note book].Species = "Blackbird" and [Atlas Field note book].Square IS NULL;
  6.  
  7.  
zmbd, the table extracts are here:

Table 1 Actual Name [Atlas Field note book] extract

Species Square Tetrad
Golden Plover SO70 C
Mute Swan SO70 C
Blackbird SO51 X
Blackbird SO70 N
Blackbird SO70 N
Herring Gull SO70 N
Herring Gull SO80 X


Table 2 Actual Name [Glos_Tetrads] extract

Square Tetrad Tetrad Flag
SO50 G Y
SO50 K Y
SO50 L Y
ST69 J Y
SU19 T Y
SU19 U Y
SU29 E Y
SU29 J Y

So Table 1 ([Atlas Field note book]) contains all of the sightings and Table 2 is purely a list of all possible Square and Tetrad combinations.

Square+Tetrad is a unique combination defining the Ordnance Survey co-ordinates of each 2k square of the UK (or British Isles?) so SU05N in your example is a unique location.
Sep 12 '12 #8
I'll try the code and tables again with proper formatting

Expand|Select|Wrap|Line Numbers
  1. SELECT [Atlas Field note book].Species, 
  2.    [Atlas Field note book].Square,
  3.    [Atlas Field note book].Tetrad
  4. FROM [Atlas Field note book] 
  5.    LEFT JOIN Glos_Tetrads 
  6.    ON (
  7.    ([Atlas Field note book].Square = 
  8.       Glos_Tetrads.Square)
  9.    AND ([Atlas Field note book].Tetrad =
  10.       Glos_Tetrads.Tetrad)
  11.        )
  12. WHERE [Atlas Field note book].Species =
  13.     "Blackbird" 
  14.     AND [Atlas Field note book].Square IS NULL;
  15.  

Expand|Select|Wrap|Line Numbers
  1. Table 1 Actual Name [Atlas Field note book] extract
  2.  
  3. Species          Square     Tetrad
  4. Golden Plover    SO70        C
  5. Mute Swan        SO70        C
  6. Blackbird        SO51        X
  7. Blackbird        SO70        N
  8. Blackbird        SO70        N
  9. Herring Gull     SO70        N
  10. Herring Gull     SO80        X
Expand|Select|Wrap|Line Numbers
  1. Table 2 Actual Name [Glos_Tetrads] extract
  2.  
  3. Square        Tetrad        Tetrad Flag
  4. SO50               G               Y
  5. SO50               K               Y
  6. SO50               L               Y
  7. ST69               J               Y
  8. SU19               T               Y
  9. SU19               U               Y
  10. SU29               E               Y
  11. SU29               J               Y
Sep 12 '12 #9
Rabbit
12,516 Expert Mod 8TB
If you're trying to get everything from Glos_Tetrads, then you need to either reverse the order in the SQL or use a right join. Then to limit to those that don't exist in [Atlas Field note book], you need to return only those where the key field in [Atlas Field note book] is null, which you have in your WHERE clause. However, if it doesn't exist in [Atlas Field note book], then species will be null as well. You can't have a record that doesn't exist and exist at the same time, which is what you've tried to do. So if you're trying to limit it to Blackbird, then you need to either include it as a condition in the join or subquery for those records first.
Sep 12 '12 #10
Hi Rabbit, I should have said, a RIGHT JOIN produced zero records with the code I used above also.

Following your advice (I think) I just tried the following:

Expand|Select|Wrap|Line Numbers
  1. SELECT [Atlas Field note book].Species, [Glos_Tetrads].Square, [Glos_Tetrads].Tetrad
  2.  
  3. FROM Glos_Tetrads LEFT JOIN [Atlas Field note book] ON (Glos_Tetrads.Tetrad=[Atlas Field note book].Tetrad) AND (Glos_Tetrads.Square=[Atlas Field note book].Square)
  4.  
  5. WHERE [Glos_Tetrads].[Tetrad Flag]="Y" And [Atlas Field note book].Species Is Null
  6.  
  7. GROUP BY [Atlas Field note book].Species, [Glos_Tetrads].Square, [Glos_Tetrads].Tetrad;
  8.  
  9.  
I don't really want to restrict it to Blackbird, that was thrown in to limit the processing for this exercise, although this latest code attempt didn't take long to produce zero records!!

I actually need those Square and Tetrad combinations that are missing for each species in the Atlas Field note book.

If you don't mind and it's easy for you to do, could you provide the SQL that would work for what I'm trying to achieve please? I feel I'm missing some fundemental understanding somewhere along the line.

Cheers, Phil
Sep 12 '12 #11
Rabbit
12,516 Expert Mod 8TB
If you don't want to limit it to a single species, then you will need to create that third table I mentioned in post #3 and #6. Once that's created, you can cross join it to your Glos_Tetrads table and then do your left join to the [Atlas Field note book].

In the end, it would be something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM locations, species
  3.    LEFT JOIN sightings
  4.    ON  locations.location = sightings.location
  5.    AND species.specie = sightings.specie
  6. WHERE sightings.specie IS NULL
The locations / species cross join will create all possible combinations of location and species, the left join with the WHERE clause will eliminate any that exist in the sightings.
Sep 12 '12 #12
Hi Rabbit, have now created a Species table (Species_Table) containing a single field - Species - and holding a record for each distinct species found in the "sightings" table ([Atlas Field note book]).

I have also combined the Square and Tetrad fields into a new field - Tetradid - in the "locations" table (Glos_Tetrads) and "sightings" table ([Atlas Field note book]).

I created the following query:

Expand|Select|Wrap|Line Numbers
  1. SELECT  Species, tetradid
  2.  
  3. FROM Glos_Tetrads, Species_Table
  4.    LEFT JOIN [Atlas Field note book] 
  5.    ON  Glos_Tetrads.tetradid = [Atlas Field note book].tetradid 
  6.    AND [Species_Table].Species = [Atlas Field note book].Species
  7.  
  8. WHERE [Atlas Field note book].Species IS NULL;
  9.  
which I think is inline with your model but I get "syntax error in JOIN operation". I tried adding [Atlas Field note book] into the FROM table list but same problem.

I'm probably being dumb but I can't see the error!
Sep 12 '12 #13
zmbd
5,501 Expert Mod 4TB
ahh... I see that Rabbit has the answer:
Here's my twist using just the two tables:
Instead of hand generating the species table. I did an agregate query on the "Atlas Field note book" by species. Now I have a list of every species that has a record in that data

I then did a cross against this query and gloss tetrads...
I then did the left join against the Atlas Field Note Book on the square and species:

and you get:

Expand|Select|Wrap|Line Numbers
  1. SELECT qry1.*
  2. FROM (SELECT Glos_Tetrads.Square,
  3.           Glos_Tetrads.Tetrad,
  4.           qry2.Species
  5.      FROM 
  6.           (SELECT Atlas_Field_Note_Book.Species
  7.            FROM Atlas_Field_Note_Book
  8.            GROUP BY Atlas_Field_Note_Book.Species) 
  9.         AS qry2,
  10.            Glos_Tetrads)
  11.         AS qry1 
  12. LEFT JOIN Atlas_Field_Note_Book 
  13. ON (qry1.Species = Atlas_Field_Note_Book.Species) 
  14.    AND
  15.    (qry1.Square = Atlas_Field_Note_Book.Square)
  16. WHERE (Atlas_Field_Note_Book.Species Is Null);
The only hitch I have here is the "Glos_Tetrads" so long as it's a query or an actual table in the database you should be ok.

-z
Sep 12 '12 #14
Hi zmbd, well that has produced an interesting result rather than zero records except I'm not sure what it has returned. It returns 90k records (Species, Square, Tetrad) which if I MAKETABLE them and Group By on the Species I get 206 distinct species out of a possible 262. The commoner species such as my beloved Blackbird is not included. It seems the species that appear in virtually all locations slip the net somehow.

Unless you and Rabbit are happy to pursue this I think I may resort to a "spot" of VBA to provide the solution.

Very many thanks to you both for you're help if you want to drop it.

Best Regards, Phil
Sep 12 '12 #15
Rabbit
12,516 Expert Mod 8TB
We can't really tell what's going on without seeing all the data from each table for at least one of the species that doesn't show up.
Sep 12 '12 #16
zmbd
5,501 Expert Mod 4TB
That's interesting... worked fine on the little-bitty tables...

opps... copied and pasted the wrong one...
Expand|Select|Wrap|Line Numbers
  1. SELECT qry1.*
  2. FROM 
  3.      (SELECT Glos_Tetrads.Square, Glos_Tetrads.Tetrad, qry2.Species 
  4.      FROM (SELECT Atlas_Field_Note_Book.Species 
  5.           FROM Atlas_Field_Note_Book 
  6.           GROUP BY Atlas_Field_Note_Book.Species)  
  7.      AS qry2, 
  8.           Glos_Tetrads)  
  9.      AS qry1 
  10. LEFT JOIN Atlas_Field_Note_Book ON 
  11.           (qry1.Tetrad = Atlas_Field_Note_Book.Tetrad) 
  12.      AND (qry1.Species = Atlas_Field_Note_Book.Species) 
  13.      AND (qry1.Square = Atlas_Field_Note_Book.Square)
  14. WHERE (Atlas_Field_Note_Book.Species Is Null);
you need that (qry1.Tetrad = Atlas_Field_Note_Book.Tetrad) in there
Personally, I would have put a record_id in that glos_tetrads table... just for that reason... I hate compound primary keys.

Lines 4 thru 6 (qry2) generate the species list from the Atlas_Field_Note_Book.Species, If you cut/paste into a new query remove the "()" and tack ";" on the end it will return a list of every species that has a record in that table. If the blackbird still doesn't show with this agregate then there's another issue and you'll have to generate the table as Rabbit suggests.
Sep 12 '12 #17
Hi zmbd, fantastic !! Absolutely bang on. A sample shows:

Expand|Select|Wrap|Line Numbers
  1. Species    Square    Tetrad
  2. Blackbird    ST69    J
  3. Blue Tit    ST69    J
  4. Greenfinch    SO92    V
  5. Greenfinch    SP11    Z
  6. Greenfinch    SP13    F
  7. Greenfinch    SP20    D
  8. Greenfinch    ST59    Y
  9. Greenfinch    ST69    J
  10. Greenfinch    ST69    Y
  11.  
Just what I would have expected and it processed in a matter of a few seconds - brilliant. Thanks so much to you and Rabbit for spending so much time on this. I even think I understand the processing now.

Best Regards, Phil
Sep 12 '12 #18

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: lawrence | last post by:
Google can't find me a good example of how to use the "if exists" syntax in MySql. Is it right that to use it this way: INSERT INTO IF EXISTS tMyTable VALUES("xxlk", "lkjlkjlkjljk") I want...
3
by: B. Harwood | last post by:
This is probably a simple question, but I'm stumped. I cannot figure out a way to do the following. For example, say I have two tables - one of used toys and one of broken toys. Say I wanted to...
4
by: Jacinle Young | last post by:
Hi all, I have written the SQL but it doesn't work db2 "select acode from t1 where (acode) not in (select eid from t2 where iid ='0') and iid ='0'" select nothing but the following works
7
by: Olaf Baeyens | last post by:
I am testing VC++ 2005 and I get this warning: "warning C4996: 'strncpy' was declared deprecated" Does that mean that they might be phased out in VC++ 2006 or higher? Or does an alternative...
0
by: Ian Staines | last post by:
In asp the following code: Request.Servervarables("AUTH_USER") will return the header variable AUTH_USER In .NET the code Request.Servervariables("AUTH_USER") returns the server variable...
5
by: Jiggaz | last post by:
Hi, Look my stored procedure : __________________ ALTER PROCEDURE dbo.CreateAccount @Nickname varchar(30), @Password varchar(15), @Email varchar(50), @Date datetime,
14
by: mike | last post by:
I'm using postgresl 7.3.2 and have a query that executes very slowly. There are 2 tables: Item and LogEvent. ItemID (an int4) is the primary key of Item, and is also a field in LogEvent. Some...
8
by: Chad | last post by:
Should links automatically appear in the "visited" color as defined by the user's IE settings without having to add any special coding? I have a situation where the link was not changing color...
3
by: NAT | last post by:
I am using session mode as "InProc"(entered in web.config). I have deployed my ASP.NET appln. on a server which uses Load Balancer. i.e I have two servers. I am using session across pages.The...
1
by: dwasler | last post by:
Try every thing I know to remove this alias I know there been other posting I read each one none seem to work. Thank You DLWasler dwasler@yahoo.com OS Window db2 V 8.2.X
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.