While working on my photo album software, I ran into an interesting SQL problem. I wanted to be able to display information about my photo albums, along with the number of images in each album. The problem is that my data is broken up into two tables: an albums table and an images table. My goal was to use exactly one SQL query to access all of the data, including the count of images. And I wanted empty albums (no images) to also show up in the query's results. But try as I might, I couldn't get the query to return the data I wanted. I finally found a solution that works, and I present an example below.

Let's suppose we have two MySQL tables: one that represents directories, and another that represents files. The directories table has the following columns:

• ID
• Name

And the files table has the following columns:

• ID
• Parent_ID
• Name

The Parent_ID field in the files table corresponds to the ID field in the directories table. In order to select both the count of files in each directory, as well as all of the directory information, we do a simple join. But here's the trick: the order of your tables matters! Here's the query that works for this scenario:

SELECT d.*, Count(f.ID) AS Count FROM directories d LEFT JOIN files f ON f.Parent_ID = d.ID GROUP BY d.ID

When the tables are reversed in the JOIN, only tables with 1 or more entries show up in the results. What a subtle change! Hopefully someone will find this tip useful. It sure took me a while to get this working.