How to query 2 tables and then perform a wildcard search using a 3

[DB Dummy]

Hi,

I hope someone can please help. I'm a relative novice to queries and SQL,
so I apologize if this is too simple a question:

I have two tables I want to query and obtain results that only list items
that differ between them. Then, I want to compare the results of that query
against a table and do a sort of wildcard seach:

LIKE "*" & [IgnoreError]![Field1] & "*"

This last part is important in order to filter out lines in the table that
contain fragments of phrases that can be excluded. (They are error messages
from an interface log).

So to summarize, I have two tables (A and B), each with two columns (the
first is an auto generated ID # and the second contains a text phrase) that I
want to compare and filter A against B and only show the uniques . Then, I
want to compare the results of that query against a table of ignore phrases
and perform a wildcard "Like" query. Hope this explains it.

Thanks.

 

[BobT]

Okay, this is going to take some work.

First, assuming you want all the unique entires - i.e. all those that are
unique to table A and to table B - then you need to create one query with a
union of the two sub-queries or three separate queries (one to get the unique
rows from A; one to get the unique rows from B and the third to combine the
result set).

Here's a union query that takes the two "unique" queries and combines them
into one query:

SELECT [table-a].text
FROM [table-a] LEFT JOIN [table-b] ON [table-a].text = [table-b].text
WHERE ((([table-b].text) Is Null))
UNION ALL
SELECT [table-b].text
FROM [table-a] RIGHT JOIN [table-b] ON [table-a].text = [table-b].text
WHERE ((([table-a].text) Is Null));

Now, once you have this query, you then query it against your "like" table
(I'm calling table-c) with a NOT clause and voila:

SELECT DISTINCT union.text AS Uniques
FROM [union], [table-c]
WHERE (((union.text) Not Like [table-c]![text]));

You get the distinct "Uniques" that do not contain the phrases in your third
table.