non-equijoin using a subquery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm attempting to exclude all records that have a value in a particular field
if any part of the text matches values in a query. Here is my attempt but
it's still bringing up all records:

SELECT item, udref FROM qrySelectedRepReturnsInXDays INNER JOIN
qryExcluedReturnItemWords ON qrySelectedRepReturnsInXDays.udref Not Like
"*" & qryExcluedReturnItemWords.ExcludeItems & "*"

Any Sugestions?
 
I'm attempting to exclude all records that have a value in a particular field
if any part of the text matches values in a query. Here is my attempt but
it's still bringing up all records:

SELECT item, udref FROM qrySelectedRepReturnsInXDays INNER JOIN
qryExcluedReturnItemWords ON qrySelectedRepReturnsInXDays.udref Not Like
"*" & qryExcluedReturnItemWords.ExcludeItems & "*"

Any Sugestions?
Click to expand...

Well, that's not working because every record has SOME portion of the
udref field that's not like the ExcludeItems value, I'd guess.

Try a "frustrated outer join" query instead:

SELECT qrySelectedRepReturnsInXDays.item,
qrySelectedRepReturnsInXDays.udref FROM qrySelectedRepReturnsInXDays
LEFT JOIN qryExcluedReturnItemWords ON
qrySelectedRepReturnsInXDays.udref Like "*" &
qryExcluedReturnItemWords.ExcludeItems & "*"
WHERE qryExcluedReturnItemWords.ExcludeItems IS NULL

John W. Vinson[MVP]
 
Dear Franky:

Your post gives me a clue. "ExcludeItems" sounds like there's some kind of
a list of items, and that there may be some kind of separator between them.

I'm thinking you have a parsing problem. First, the LIKE operator doesn't
separate the search string into portions and search for each one. It just
searches for the whole string one time. That doesn't sound at all similar
to what I have pictured as what you want.

Another issue is whether you are searching for "words" or are searching for
any matching pattern. If you are searching for "turn" is "returns" a match?

I expect you'll need to write a custom function to do this. For Jet, you
write that in VBA. For MSDE/SQL Server, you write a User Defined Function.

As with the LIKE operator, such frunctions will result in a "table scan"
since they cannot do their job using any type of indexing.

Your search strings are in a related table. Instead of throwing all the
search words into a single column, you should put one search string in each
row of the table. This could then be built as a NOT EXISTS on a subquery.
Depending on your definition of a match (per my question above) you may
still need a function to do the search (the turn/returns question above).

Tom Ellison
 
Hi,


Is short, your solution with strict = would have been

WHERE udref NOT IN( SELECT ExcludeItems FROM qryExcluedReturnItemWords)


but rather than having equality, you have patterns and need to use LIKE.
Right? If so...



FIRST, why your solution does not work.


The ON clause is logically evaluated AFTER the cartesian join is done.

As example. if qrySelectedRepReturnsInXDays.udref = { 1, 2, 3} and
qryExcluedReturnItemWords.ExcludeItems ={ 2, 101, 103}


then, after the cartesian join, we have

udref exlcudeItems
1 2
1 101
1 103
2 2
2 101
2 103
3 2
3 101
3 103


then, evaluate the ON clause, here, for simplicity, say: ON udref <>
excludeItems


udref exlcudeItems ON_evaluation
1 2 true
1 101 true
1 103 true
2 2 false
2 101 true
2 103 true
3 2 true
3 101 true
3 103 true


and we keep all those produced records (produced by the cartesian join)
where the ON evaluates to TRUE... we see that only one result of the
cartesian product will be removed. In fact, if we have replaced the <> by a
=, only one row will be kept, so, the two cases, <> and =, are somehow
"complementary" (because there are no null values).


NOW, how to make it to work.

Note that with <>, we removed ONE record from the cartesian join. We also
have data "in excess". So, what about a GROUP BY to solve these two
problems:

a possible solution is to GROUP BY udref, and to COUNT the number of records
after the ON clause:


SELECT udref, LAST(item)
FROM ... ON ...
GROUP BY udref
HAVING COUNT(*)= ( SELECT COUNT(*) FROM qryExcluedReturnItemWords)


Indeed, the record with

qrySelectedRepReturnsInXDays.udref Not Like "*" &
qryExcluedReturnItemWords.ExcludeItems & "*"


will have at least ONE LESS record (as we illustrated) than there are in the
original qryExcluedReturnItemWords, and so, has to be removed from the final
result to get what we want! (I assume).




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top