Counting Records from 3 tables, 2 of which are joined together by a common data element
We want to display a list of Newsletter Topics, and show how many active subscribers there are for each topic.
You have 3 tables.
Topics ÃƒÂ¢Ã¢â€šÂ¬Ã¢â‚¬Å“ a list of topics for newsletters
Subscribers ÃƒÂ¢Ã¢â€šÂ¬Ã¢â‚¬Å“ a list of subscribers to newsletters
Subscribers_Topics - a table to join subscribers to Topics
An INNER JOIN can only join 2 tables together.
We need to join 3 tables together. So we must ÃƒÂ¢Ã¢â€šÂ¬Ã…â€œnestÃƒÂ¢Ã¢â€šÂ¬Ã‚Â the first join between 2 tables inside the first inner join.
INNER JOIN (
Subscribers as NS
Subscribers_Topics AS NST
We use GROUP BY to combine rows that have the same field values, so we can get a count of them.
We use HAVING as a filter to limit the records that are returned. This is the same theory as using the WHERE operator, but since we are using GROUP BY, we need to use the HAVING function to limit the results.
The Code Download
- COUNT(NST.Subscriber_ID) AS Subscribers
- Topics NT
- INNER JOIN (
- Subscribers AS NS
- INNER JOIN
- Subscribers_Topics AS NST
- GROUP BY
- HAVING (
- ORDER BY
Added by JC on 14th November 2007
There are no comments about this snippet.
Post Comment Github Markdown Supported
You must be logged in to post a comment.
Login here to post a comment