|
|
Posted by neutralm@gmail.com on 08/29/06 00:19
Thank you very much, Ed. I really appreciate how quickly you've help me
fix this problem!
Ed Murphy wrote:
> 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]
|