|
|
Posted by Ed Murphy on 08/28/06 23:15
neutralm@gmail.com wrote:
> The query string I'm using is:
>
> select tags.id, taggings.tag_id, name, count(*) as count from taggings,
> tags where tags.id = taggings.tag_id group by taggings.tag_id
>
> How should the correct query look like?
select taggings.tag_id, name, count(*) as tag_id_count
from taggins join tags on taggings.tag_id = tags.id
group by taggings.tag_id, name
Explanations:
1) GROUP BY must include all unaggregated columns from the SELECT,
i.e. everything that is not a COUNT(), SUM(), etc. (Why doesn't
it implicitly assume this? Apparently, it used to let you leave
things out, but that caused more trouble than it was worth. The
short answer is "just give it what it wants".)
2) tags.id and taggings.tag_id are forced to be equal, so you only need
to include one of them. Optional but recommended, as it's simpler
and conserves bandwidth.
3) The join is changed from SELECT ... FROM A, B WHERE A.X = B.Y
to SELECT ... FROM A JOIN B ON A.X = B.Y
Optional but recommended, as it keeps join conditions separate from
each other, and from other restrictions (e.g. NAME LIKE '%ABC%'),
all of which makes the query easier to understand.
Navigation:
[Reply to this message]
|