Query for three tables with 1 condition
I have the following tables:
tags
id tag_name
<strong> examples
category heading id
examples_tags
id tag_id example_id
How can I get the number of examples under each tag? (a bit like stackoverflow actually :))
I also want an additional condition like: examples.category = "english examples"
This is how I got 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.
0
a source to share
2 answers
Without joins, the correct one would be:
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 all non-aggregated fields.
Otherwise, you can use an inner join, making the request 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
+1
a source to share
It is a 3-table join using many-to-many example tables in the middle between tags and example tables. You must also group by each 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
+1
a source to share