Hi--
I'm running an art archive, and for the artist listing pages I have to
come up with a query something like the following:
select name,dirname,sortname,pictures.artistid,count(pictures.artistid)
as imgnum,avg(rating) as rateavg,max(uploaded) as lastmod
from artists,pictures,ratings
where artists.id=pictures.artistid and artists.id=ratings.artistid
group by sortname
order by imgnum desc
There are three tables involved: "artists", "pictures", and "ratings".
"artists" has all unique entries (identified by "id"). "pictures" has
thousands of entries with multiple entries per "artistid". "ratings" has
multiple entries per "artistid". I have to count the entries in
"pictures" per "artistid", and I have to average the entries in
"ratings" per "artistid".
The problem is that running the query as I have it above, the "imgnum"
variable (count(pictures.artistid)) is coming back vastly higher than
what I need, which is the number of pictures-- e.g. the number of
entries in the "pictures" table with "artistid" matching the artist it's
grouping by.
I think the problem has something to do with the "where" spec; I guess
I'm just not conceptualizing the problem properly. I need to get all
this data in one fell swoop, or else my scripts will get much slower and
more complex. Is there any way to get COUNT() to count only the rows
within a grouping? Or am I going about this all the wrong way?
Many thanks in advance for any insight!
Brian Tiemann
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
