SQL Snippets

Goal:

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

ID	Name	Subscribers
1	Horse Breeding	1
2	Show Training	1
6	Officers	3

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

Each table has data columns as follows:

Topics

ID	Name	Active	List_Position
1	Horse Breeding	Yes	3
2	Show Training	Yes	4
3	Members	No	5
6	Officers	No	8

Subscribers

ID	Last_Name	Email_Address	Subscribe
66	Washer	test@amazon.com	No
67	Cotton	cotton@netsync.net	Yes
68	Tide	tide@appliance.com	Yes
69	Ring	ring@wisk.com	Yes
70	Spin	spin@cycle.com	Yes
73	Dryer	Dryer@amana.com	No

Subscribers_Topics

ID	Subscriber_ID	Topic_ID
12	66	1
13	66	2
14	66	3
15	73	1
16	73	2
17	73	6
18	67	6
19	70	6

SQL Query:

SELECT NT.ID, NT.Name, Count(NST.Subscriber_ID) AS Subscribers

FROM Topics as 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

Results:

ID	Name	Subscribers
1	Horse Breeding	1
2	Show Training	1
6	Officers	3

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 = NST.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.

Without GROUP BY our results list would look like this:

ID	Name
1	Horse Breeding
1	Horse Breeding
2	Show Training
2	Show Training
3	Members
6	Officers
6	Officers
6	Officers

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.