I'm building a simple photo gallery for my website, and I've run into a minor hitch. I can't seem to figure out how to get certain records to display in the order I want them to.
My photo gallery will have an albums table (album_id, parent_album, album_name, album_desc), and albums can have "children" (thus the "parent_album" column). So I can have something like this:
Christmas is an album, and the years are sub-albums within it. When I do a query, I want the sub-albums to display under their "parents", and not intermingled with all the other albums. This is the query I've started off with:
This gives me the name of the album, the children, and the number of pics in each album, but the ORDER BY clause doesn't do what I want. Is this something I need to handle in the code and not the query?
, a.album_name AS parent
, ( SELECT COUNT(pic_id)
WHERE pic_album = albums.album_id ) AS pics
LEFT JOIN albums AS a
ON a.album_id = albums.parent_album
ORDER BY parent_album, album_name