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

I'm currently building a custom photoalbum but now I'm stuck with a mysql query. The idea of the query is to fetch a list of albums, count the number of pictures in it, and fetch one thumbnail per album.

The database now consist of two tables, one containing the album data and one containing the pictures (I store them in the database).

Table: photoalbums

id | album_name | album_created
-------------------------------------
1  | testalbum  | 2010-11-07 19:33:20
2  | some 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 开发者_如何学C | binary | binary    | test5 | 1

My current query looks like this but clearly doesn't work.

SELECT alb.id album_id, alb.album_name, alb.album_created, COUNT(p.id) pcount FROM photoalbums alb LEFT JOIN pictures p ON p.album_id=alb.id GROUP BY p.album_id ORDER BY alb.album_name ASC

First of all, this query would fetch all of the thumbnails within the album, and I only want one (as a preview). Next, it seems to stop at one album and I think it's the GROUP BY statement which is causing that.

Do I need to perform an extra query for each album whithin the while() loop to fetch a single thumbnail per album, or can I just do it with a single query? And what is the error causing just one album to appear in the recordset?

Thanks in advance!

---

Typically, I'd think you would want to group by all non-aggregated columns in the SELECT list in order to get expected results. And the first way that occurs to me of selecting one (arbitrary) thumbnail per album would go like this (untested):

SELECT alb.id AS album_id, alb.album_name, alb.album_created, 
       COUNT(p.id) AS pcount, 
       MAX(p.thumbnail) AS thumbnail
FROM photoalbums alb 
LEFT JOIN pictures p ON p.album_id=alb.id 
GROUP BY alb.id, alb.album_name, alb.album_created
ORDER BY alb.album_name ASC

---

This might not be the answer, but I think you are missing an AS statement when you alias your fields.

Logically, this query should work, though I haven't tested it:

SELECT photoalbums.ID, photoalbums.Name, photoalbums.Created, Count(photoalbums.ID) AS pCount
FROM photoalbums
INNER JOIN pictures ON pictures.album_id = photoalbums.id
GROUP BY pictures.album_id
ORDER BY photoalbums.album_name ASC

Or you could do a sub query:

SELECT thumbnail FROM pictures
WHERE ID IN (SELECT ID FROM phtoalbums)
GROUP BY album_id

Though it's best practise to not do subqueries if you can avoid them.