"Janelle" <(E-Mail Removed)> wrote in message
news:087801c3bf43$201a3b90$(E-Mail Removed)
> 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.
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
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
Similar Threads
