Query on three tables with 1 condition

Question

I have the following tables:

tags

id tag_name

examples

id category heading

examples_tags

id tag_id example_id

How can I retrieve the number of examples under each tag? (a bit like stackoverflow actually :))

I also want an additional condition of the type: examples.category = "english examples"

This is how I started ...

SELECT tags.id, tags.tag_name, COUNT( examples_tags.tag_id ) AS 'no_tags'
WHERE tags.id = examples_tags.tag_id

&&
examples.category = 'english examples'

GROUP BY tags.id

Thanks .

Answer 1

This is a 3 table join, using the many-to-many table examples_tags in the middle between the tags and examples tables. You also have to group by every column that is not an aggregate in the select list.

SELECT t.id, t.tag_name, COUNT( *) AS 'no_tags'
FROM tags t
 JOIN examples_tags et
   ON t.id = et.tag_id
 JOIN examples e
   ON e.example_id = e.id
WHERE 
  e.category = 'english examples'
GROUP BY t.id, t.tag_name
ORDER BY t.tag_name

Answer 2

Without joins correct would be next:

SELECT tags.id, tags.tag_name, COUNT(*) AS num_tags
FROM tags, examples_tags, examples
WHERE tags.id = examples_tags.tag_id 
    and examples_tags.example_id=examples.id
    and examples.category = 'english examples'
GROUP BY tags.id, tags.tag_name

You need to group by all non-aggregated fields.

Otherwise you could use inner join, makes query more readable:

SELECT tags.id, tags.tag_name, COUNT(*) AS num_tags
FROM tags 
    inner join examples_tags on examples_tags.tag_id=tags.id
    inner join examples on examples_tags.example_id=examples.id
WHERE examples.category = 'english examples'
GROUP BY tags.id, tags.tag_name