mysql - How to select photoalbums, count photo's, and select one photo per album? -

i'm building custom photoalbum i'm stuck mysql query. idea of query fetch list of albums, count number of pictures in it, , fetch 1 thumbnail per album.

the database consist of 2 tables, 1 containing album data , 1 containing pictures (i store them in database).

table: photoalbums

id | album_name | album_created ------------------------------------- 1  | testalbum  | 2010-11-07 19:33:20 2  | more  | 2010-11-15 18:48:29 

table: pictures

id | file   | thumbnail | name  | album_id ------------------------------------------ 1  | binary | binary    | test1 | 1 2  | binary | binary    | test2 | 1 3  | binary | binary    | test3 | 2 4  | binary | binary    | test4 | 2 5  | binary | binary    | test5 | 1 

my current query looks doesn't work.

select alb.id album_id, alb.album_name, alb.album_created, count(p.id) pcount photoalbums alb left join pictures p on p.album_id=alb.id group p.album_id order alb.album_name asc 

first of all, query fetch of thumbnails within album, , want 1 (as preview). next, seems stop @ 1 album , think it's group statement causing that.

do need perform query each album whithin while() loop fetch single thumbnail per album, or can single query? , error causing 1 album appear in recordset?

thanks in advance!

typically, i'd think want group non-aggregated columns in select list in order expected results. , first way occurs me of selecting 1 (arbitrary) thumbnail per album go (untested):

select alb.id album_id, alb.album_name, alb.album_created,         count(p.id) pcount,         max(p.thumbnail) thumbnail photoalbums alb  left join pictures p on p.album_id=alb.id  group alb.id, alb.album_name, alb.album_created order alb.album_name asc