Count Related Records satisfying criteria
We have a database of book data.
In the main Books table, we have a field, Season, which holds the season that a book is being launched (for example S15).
We also have a Subjects table listing 40 or so different subjects. A book can have many subjects and a subject can be attached to many books, so we use another table BookSubjectLink to act as the link table to facilitate the many-to-many link.
What I would like to see is a list of all subjects, together with the count of the number of books in each subject which have S15 for their season.
So, I'd see something like:
World War 1 15
World War 2 10
Can anyone tell me how to do this?