Count of related items in a 2nd table with zero results needed (query check please)

This MySQL statement is a bit 开发者_运维技巧over my head. I pieced it togather through a lot of Google searches. It seems to work right but I just wanted to see if I could get a thumbs up. I'm paranoid I did something a bit off and some issue could come up I'm not understanding.

I have a 'directories' table, 'folders' table and 'documents' table. (directories have many folders, folders have many documents).

On a web page, I have a select where a user can choose a directory (which has many folders). This query is for an AJAX call that loads a second select with the list of all folders belonging to the directory (getting the id's and names to load the 'folders' select).

So, this query will be made against one directory to get a list of folder id's and folder names for that directory. I also needed the folder name to contain a count of how many documents are contained in each folder. Also, I originally had just "join" which did not return zero results but changing it to "left join" listed folders with 0 documents (don't have an understanding of the different types of joins yet).

MY FRANKEN-QUERY:

SELECT f.id, CONCAT(f.folder_name , ' (', COUNT(DISTINCT d.id), ' documents')') AS folder_name
FROM folders f 
LEFT JOIN documents d ON d.folder_id = f.id
WHERE f.directory_id = '2' 
GROUP BY f.id
ORDER BY f.folder_name

RESULTS (seems to work fine):

id  folder_name      
1   MAIN (2 documents)   
8   test1 (2 documents)  
9   test2 (3 documents)  
50  test3 (0 documents) 

Thanks - much appreciated!

It looks fine offhand, but just run a couple tests on your data ans make sure you get consistent (correct) results.

Assuming document.id is a primary key, you can remove the DISTINCT keyword from the count.

For more on the various join types http://en.wikipedia.org/wiki/Join_%28SQL%29