Not in (SELECT ... FROM ...) - is there a better way?

hi everyone,

i've made a few nested queries which find unique IDs in one query, that don't appear in another query.

to do this, i simply use the Not In () function in the criteria row of a new query. (image 1)



however, these "not in" queries take a bit of time to resolve - not usually a problem, a few seconds or tens of seconds and i have the resulting list.

now i want to make a 'basic' select query based off of two of those "not in" queries - all of which are set to return "unique values" - this is fine, still takes a little while to return the list, but ok. (image 2)



however, now i'm making a query to see which records are not in another query which is selected off a third "not-in" query. (if you get my meaning - i think what i'm doing is pretty much nesting lots of queries - don't know if this is strictly the same as a 'subquery')

now, this final query with all the 'nesting' is taking FOREVER to run (more so than the two other queries put together) and when i do get the datasheet view of that query up and try to scroll down the list to get to the bottom, it freezes access and i have to force it to close. (image 3)



my question is: am i doing this the long, not clever, or naive way? is there a better way to do these subqueries?

i need the subqueries as stand-alone queries themselves, because they are useful in their own right for my work, and referencing them via the Not In () function is 'easy'... but is it the best way to get this kind of data?

not sure whether this is relevant, but i'll mention it: some of the queries that i'm referencing in the criteria row of other queries can be based, in one shape or another on the same table...

e.g., i have a table. i make a query which selects some samples from this table. the next query selects samples from the table which do not appear in the first query.

- the thing that could suck if this is the only way to do it is that i will need to make further select queries from these, and would really prefer not to have to wait minutes for a query to run...

always needy...
agnieszka.

The common answer:

Do a frustrated join.



So, instead of this:


We do this:


Both produce same result but the engine is usually more optimized for joins rather than subqueries so frustrated joins gives better performance. Since the nonmatch for a outer join will return null, we use that to test if there was a matching row or not.

Hope that helps.

that seems to do the trick, thanks banana

still running a little slowly, but probably only because the queries referred to in the SQL are also "not-in" queries - i'll change them to "frustrated joins" and see if that improves the overall performance.

ha! what a good name for a join like that!

edit: and once again Access Help proves to be utterly useless in providing ANY information on 'frustrated' joins!

You must spread some Reputation around before giving it to Banana again.

Click to expand...

wow - i just changed the other query from "not-in" to a "frustrated join" - the resulting query resulted instantaneously! this is the one which would normally take about 5 min to 'think', then freeze the system!

you beaut! (can you tell i'm aussie? LOL)

and for newbies who are daunted by SQL, here's a snapshot of a 'frustrated' left join in design view.




note: the join properties between these two entities is set to return all records from tblCHROMpatches (this is when the arrow appears instead of just a line).

this means that when BoxID is set to "Is Null" from the "qryALLgent" entitiy, only those BoxID records from tblCHROMpatches are returned if they are NOT already in the "qryALLgent".

cool. thanks for that banana. i've seen my supervisor do this kind of query before, but i hadn't mentally connected the dots.

wikilendt-

It's weird that somehow I missed all of your replies!

But I'm so glad you got it worked out and great to see you sharing that screenshot for benefits of others!

BTW, to clarify... 'frustrated join' isn't really a formal term. I'd be surprised if it was included in *any* help files!

Banana said:

wikilendt-

It's weird that somehow I missed all of your replies!

But I'm so glad you got it worked out and great to see you sharing that screenshot for benefits of others!

BTW, to clarify... 'frustrated join' isn't really a formal term. I'd be surprised if it was included in *any* help files!

Click to expand...

no worries - the replies were just my thought train

i *was* wondering about the terminology... what IS it's formal term? does it even have one? i know they're just left and right joins followed by a null criterion on one of the fields, but i love the term "frustrated join"!! it's soooooo "Access"!! LOL

Beat me if I knew. I only got that from another MVP, Leigh Purvis (you may have seen him posting here occasionally with username LPurvis).

yeah i know of leigh purvis. helps out A LOT around here. i think even helped me directly a couple of times? anyway, thanks again for your help