Counting Records from 3 tables, 2 of which are joined together by a common data element

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

  1. SELECT
  2.     NT.ID,
  3.     NT.Name,
  4.     COUNT(NST.Subscriber_ID) AS Subscribers
  5. FROM
  6.     Topics NT
  7.   INNER JOIN (
  8.     Subscribers AS NS
  9.   INNER JOIN
  10.     Subscribers_Topics AS NST
  11.   ON
  12.     NS.ID=NST.Subscriber_ID
  13.   )
  14. ON
  15.     NT.ID=NST.Topic_ID
  16. GROUP BY
  17.     NT.ID,
  18.     NT.Name,
  19.     NS.Subscribe,
  20.     NT.List_Position
  21. HAVING (
  22.     (NS.Subscribe)=TRUE
  23.   )
  24. ORDER BY
  25.     NT.List_Position

Credits

Added by JC on 14th November 2007

Comments

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