Counting Records from 3 tables, 2 of which are joined together by a common data element Login or Register to Bookmark this snippet

Description SQL

Goal:

We want to display a list of Newsletter Topics, and show how many active subscribers there are for each topic.

Table Structure:

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

Explanation:

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
INNER JOIN
Subscribers_Topics AS NST
ON NS.ID=ST.Subscriber_ID
)
ON
NT.ID=NST.Topic_ID

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

SELECT
    NT.ID,
    NT.Name,
    COUNT(NST.Subscriber_ID) AS Subscribers
FROM
    Topics NT
  INNER JOIN (
    Subscribers AS NS
  INNER JOIN
    Subscribers_Topics AS NST
  ON
    NS.ID=NST.Subscriber_ID
  )
ON
    NT.ID=NST.Topic_ID
GROUP BY
    NT.ID,
    NT.Name,
    NS.Subscribe,
    NT.List_Position
HAVING (
    (NS.Subscribe)=True
  )
ORDER BY
    NT.List_Position

Snippets v2

Counting Records from 3 tables, 2 of which are joined together by a common data element Login or Register to Bookmark this snippet