[SOLVED] Not pulling correct number of records from joined tables

[Buchead]

Hello all,

 

Hopefully someone can point out my glaring mistake as I can't see it. I have 2 tables:

 

photofolders:

 

folderID

folderName

folderPosition

 

photos:

 

photoID

photoImage

photoFolder

 

I'm attempting to pull out a list of all the folders, ordered by their folderPosition, and give a count of the number of photos in each of them. I've come up with:

 

SELECT f.*,COUNT(p.photoID) AS ct FROM photofolders AS f LEFT JOIN photos AS p ON f.folderID=p.photoFolder GROUP BY p.photoID ORDER BY f.folderPosition ASC

 

This returns only 3 folders no matter how many are in photofolders. The count is correct for those folders though!  I've tried changing the variable in COUNT to p.photoFolder, and the GROUP BY to match, but it always produces the same count.

 

What have I done wrong?

 

Many thanks,

 

Clive.

[fenway]

That's because COUNT() doesn't count nulls... and the p table can be nullified by the left join...

[Buchead]

Thanks. Will work on an alternative solution.

[fenway]

Thanks. Will work on an alternative solution.

You can simply count(*) or use the f table fields...