Compare 2 field values with LIKE

U

Ulrich Sommer

Hi,

I want to compare 2 field values with NOT LIKE:

Table 1 contains fields with strings like

Adobe Acrobat
Adobe Acrobat Reader
Quick Time
Quick Time Editor

Table 2 contains the key worrds that I want to suppress
like

Reader
Player

The resulting query should only show

Adobe Acrobat
Quick Time

Since the key word list will be several 100 words long, I
do not want to include them into the query as strings like

NOT LIKE *Reader* OR NOT LIKE *Player*.

I tried stuff like

NOT LIKE "*"&[Table2].[keyword]&"*"

but that does not work.

Any idea, even totally different, how to get to the
result?

Thanks,


Ulrich
 
J

John Spencer

You could try the following UNTESTED variations. They should all work, but
one may be faster than the others. Make sure that Table2.FieldMatch does
not contain a null value or a zero length string (zls) or you will have no
results returned since the null/zls field will end up creating a non-match
for every record in table1.

SELECT Table1.*
FROM Table1 Left Join Table2
ON Table1.FieldMatch Like "*" & Table2.FieldMatch & "*"
WHERE Table2.FieldMatch is Null


Or

SELECT Table1.*
FROM Table1
WHERE Table1.FieldMatch In
(SELECT Table1.FieldMatch
FROM Table1 INNER JOIN Table2
ON Table1.FieldMatch Like "*" & Table2.FieldMatch & "*")

Or

SELECT Table1.*
FROM Table1, Table2
WHERE Table1.FieldMatch Not Like "*" & Table2.FieldMatch & "*"
 
G

Guest

Hello John,

thanks for your reply.
This one works:
SELECT Table1.*
FROM Table1 Left Join Table2
ON Table1.FieldMatch Like "*" & Table2.FieldMatch & "*"
WHERE Table2.FieldMatch is Null
Click to expand...


This one does not work - the query never stops running
SELECT Table1.*
FROM Table1
WHERE Table1.FieldMatch In
(SELECT Table1.FieldMatch
FROM Table1 INNER JOIN Table2
ON Table1.FieldMatch Like "*" & Table2.FieldMatch
Click to expand...
& "*")


This one does not work - it shows all rows from Table 1
SELECT Table1.*
FROM Table1, Table2
WHERE Table1.FieldMatch Not Like "*" & Table2.FieldMatch
Click to expand...
& "*"


Strangely enough, the one that works gives an error that
is can not represent the query in design view (A02), but
not matter.

Great help,
Thanks.
 
Top