Using COUNT as a sub query within a SELECT statement

Hi,

I have a simple select statement that fetches a list of “pages” from a MySQL table (tblpages).

"SELECT pgId, pgMenuTitle FROM tblpages"

Each “page” can have a number of banner slideshow images assigned to it and these are stored in another database table (tblsliders). The “related” field between the two tables is pgId (the unique auto increment ID of tblpages) and slidepgId which is the “pgId” value stored in the tblsliders table.

What I’d like to do is have my initial recordset return the pgId (ID), pgMenuTitle (Menu Title) and the number of slides (anyslides) assigned to each page (retrieved from tblsliders for each row in the tblpages table) (slideId is the unique auto increment ID of tblsliders).

I tried the following but the recordset now only returns 1 row (there should be about 20):

"SELECT pgId, pgMenuTitle, COUNT(slideId) AS anyslides FROM tblpages LEFT JOIN tblsliders ON slidepgId = pgId" 

Can anyone help.

Regards,
A

you forgot the GROUP BY clause

Hi,

Many thanks - that did the trick.

Regards
AP