need help with a query

[Chris Ellis]

Hi,

I have 3 tables: Notes, Categories and NotesToCats. Notes and
Categories both have ID fields and NotesToCats is a linking table to
establish a many-to-many relationship between Notes and Categories. I am
trying to come up with a query that will yield me only unique notes that are
connected with a given number of categories.

Ok, for example... say I had a list of category ID's: 12, 15 and 77. I need
a query that will return me every note that is connected to all three of the
given categories (12, 15 and 77).

Notes table has: ID, Note, Title, Date
NotesToCats has: NoteID, CategoryID
Categories has: ID, Category

I have been wearing myself out trying different combinations of COUNT, GROUP
BY and HAVING... and nothing seems to be working... the list of categories
will be variable... both in the categories and in the number of categories,
but I am building the SQL string manually, so I know how many categories and
what their ID's are at the time that I am building the query.

Thank you for any help in advance,
Chris

 

[John Viescas]

SELECT Notes.*
FROM Notes
WHERE ID IN
(SELECT NoteID FROM NotesToCats
WHERE NotesToCats.CatID IN (12, 15, 77)
GROUP BY NoteID
HAVING COUNT(*) = 3)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[Chris Ellis]

John,

that worked like a charm! Thank you very much...

Chris