SQL COUNT records in table 2 JOINS away
- by Fred K
Using MySQL, I have three tables:
projects:
ID name
1 "birthday party"
2 "soccer match"
3 "wine tasting evening"
4 "dig out garden"
5 "mountainbiking"
6 "making music"
batches:
ID projectID templateID when
1 1 1 7 days before
2 1 1 1 day before
3 4 2 21 days before
4 4 1 7 days before
5 5 1 7 days before
6 3 5 7 days before
7 3 3 14 days before
8 5 1 14 days before
templates:
ID message
1 "Hi, I'd like to invite ..."
2 "Dear Sir, Madam, ..."
3 "Can you please ..."
4 "Would you like to ..."
5 "To all dear friends ..."
6 "Does any of you guys ..."
I would like to display a table of templates and the number of projects they're used in.
So, the result should be:
templateID projectCount
1 3
2 1
3 1
4 0
5 1
6 0
I've tried all kinds of SQL queries using various JOINs, but I guess this is too complicated for me. Is it possible to get this result using a single SQL statement?
