CODEWITHSUNDEEP
sqlms-access
bernando_vialli
bernando_vialli

Reputation: 1019

Filtered Out Values Using "NOT EXISTS" when doing a JOIN with a different table

Let's say I am joining two tables on a column called "Searched" in both tables. However, I want the output to be limited to where that join from my Catalog table is not equal to any value in another third table called Part1 (well actually I am interested in comparing it to about 5 other tables) but at least to get it to work for one for a start. Currently, the query I am running (in MS Access) is outputting nothing, why is that? I was expecting to see a JOIN with 2 columns reduced by the values that are equal between the "Searched" in Cat2 and Part4.

SELECT DISTINCT Searches.Search, Cat2.Category
FROM Searches LEFT JOIN Cat2
ON Cat2.Searched = Searches.Search
WHERE NOT EXISTS (SELECT Part4.Searched FROM Part4 INNER JOIN Cat2 ON Cat2.Searched = Part4.Searched);

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

It is not outputting anything because the NOT EXISTS subquery is always returning at least one row.

The reason is because you are doing a JOIN, instead of using a correlation clause. So, try this:

SELECT DISTINCT Searches.Search, Cat2.Category
FROM Searches LEFT JOIN
     Cat2
     ON Cat2.Searched = Searches.Search
WHERE NOT EXISTS (SELECT 1
                  FROM Part4
                  WHERE Cat2.Searched = Part4.Searched
                 );

The reference Cat2.Searched in the subquery now refers to the Cat2 in the outer query. That is the logic you are looking for.

For performance, you want indexes on Part4(Searched) and Cat2(Searched, Category).

Upvotes: 1

Related Questions