List items with no match

  • Thread starter Thread starter Janelle
  • Start date Start date
J

Janelle

I have a table with a list of brands.
I have another table with a list of categories.
I have a third table which creates a many-to-many
relationship between the two.

Now, I know how to make a query that will show all the
categories associated with Brand 1. But how do I get a
list of all the categories that are NOT associated with
Brand 1?

Everything I've tried so far gets fouled up by matches
with other brands.
 
Janelle said:
I have a table with a list of brands.
I have another table with a list of categories.
I have a third table which creates a many-to-many
relationship between the two.

Now, I know how to make a query that will show all the
categories associated with Brand 1. But how do I get a
list of all the categories that are NOT associated with
Brand 1?

Everything I've tried so far gets fouled up by matches
with other brands.
Click to expand...

Suppose that your tables are named Brands, Categories, and
BrandsCategories (the linking table). The first thing you need is a
query that returns all the Categories a query that selects all
Categories from BrandsCategories where Brand = 1:

SELECT
BrandsCategories.BrandID
FROM
BrandsCategories
WHERE BrandsCategories.BrandID=1

Now you need to create a "find unmatched" query that joins this query
with Categories and returns all Categories records that are unmatched.
You could save the above query as a storder query and then use the Find
Unmatched Query Wizard to build the final query, or you could take the
above SQL statement and use it as a subquery in a single query:

SELECT
Categories.*
FROM
Categories
LEFT JOIN
(SELECT BrandsCategories.BrandID
FROM BrandsCategories
WHERE BrandsCategories.BrandID=1) As T
ON Brands.BrandID = T.BrandID
WHERE T.BrandID Is Null
 
Back
Top