Buchead Posted January 23, 2008 Share Posted January 23, 2008 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 24, 2008 Share Posted January 24, 2008 That's because COUNT() doesn't count nulls... and the p table can be nullified by the left join... Quote Link to comment Share on other sites More sharing options...
Buchead Posted January 24, 2008 Author Share Posted January 24, 2008 Thanks. Will work on an alternative solution. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 24, 2008 Share Posted January 24, 2008 Thanks. Will work on an alternative solution. You can simply count(*) or use the f table fields... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.