[thelist] [mysql] count query

[David Kaufman]

kris burford <kris at midtempo.net> wrote:
>
> i've got two tables, one containing image information with an id for
> which folder they appear in, the other the folder data
>
> i've what i thought of as a simple request to get a list of the
> folders ordered by the number of images they contain and including
> those folders which contain no images at all.
>
> my sql query as it stands is:
>
> select f.folder, f.id, count(i.id) as count from folders f left join
> images i on f.id = i.folder_id where f.auth_user = '$auth_user' order
> by count
>
> but it doesn't want to play ball.

"not playing ball" is a bit difficult to diagnose.  what do you get in
the count column? nulls?  zeroes?  some correct counts and some
incorrect?  sql errors?  ...the string "i don't want to play baseball"?

anyway the looks good to me.  maybe the "nullness" of the folder records
that have no matching image records is messing up your count.  if that's
the case you might try something like:

  select
    f.folder,
    f.id,
    sum if(isnull(i.id),0,1) as count
  from
    folders f
    left join images i on f.id = i.folder_id
  where
    f.auth_user = '$auth_user'
  order by
    count

hth,

-dave