I'm writing a select statement and I'm having trouble getting it to give me exactly what I want. I have two tables, one for folders, and one for photos. The 'folder' is a logical grouping of the photos. As someone uploads a photo, they have to choose a folder to place it in. The folder info is kept in the 'folder' table, and the photo info is kept in the photo table. Every photo will have a parent folder_id which relates to the folder table.
I'm trying select and display all of the folders a user has. I can do a simple select, and that works fine. But I want to also get the number of photos associated to each folder. So I want to do a select to get all of the folders owned by a particular member, and join that select to the photo table to not only get the folders, but count the amount of records in the photo table that have each folder as it's parent.
I have a sql statement that does this, however, if the folder has ZERO matching photos (aka the folder is empty), the folder is not returned with the select. Looking at the select statement below, how can I get it to return folders that have zero matches in the photo table?
Here are the two tables followed by the select statement
Photo_folders
Photos
Thoughts?
I'm trying select and display all of the folders a user has. I can do a simple select, and that works fine. But I want to also get the number of photos associated to each folder. So I want to do a select to get all of the folders owned by a particular member, and join that select to the photo table to not only get the folders, but count the amount of records in the photo table that have each folder as it's parent.
I have a sql statement that does this, however, if the folder has ZERO matching photos (aka the folder is empty), the folder is not returned with the select. Looking at the select statement below, how can I get it to return folders that have zero matches in the photo table?
Here are the two tables followed by the select statement
Photo_folders
Code:
[folder_id] [int] IDENTITY(1,1) NOT NULL,
[member_id] [int] NOT NULL,
[folder_name] [varchar](255) NOT NULL,
[thumbnail_url] [varchar](255) NULL,
[thumbnail_file] [varchar](255) NULL,
[created] [datetime] NOT NULL,
[created_ip] [varchar](15) NOT NULL,
[last_modified] [datetime] NULL,
[last_modified_ip] [varchar](15) NULL,
[folder_position] [smallint] NOT NULL,
[folder_security] [smallint] NOT NULL
Photos
Code:
[photo_id] [int] IDENTITY(1,1) NOT NULL,
[member_id] [int] NOT NULL,
[folder_id] [int] NULL,
[public_url] [varchar](255) NOT NULL,
[private_url] [varchar](255) NOT NULL,
[photo_file] [varchar](255) NOT NULL,
[created] [datetime] NOT NULL,
[created_ip] [varchar](15) NOT NULL,
[last_modified] [datetime] NULL,
[last_modified_ip] [varchar](15) NULL,
[photo_position] [smallint] NOT NULL,
[photo_security] [smallint] NOT NULL
Code:
SELECT DISTINCT tmpf.folder_id as folder_id,
tmpf.folder_name as folder_name,
tmpf.thumbnail_url as thumbnail_url,
tmpf.thumbnail_file as thumbnail_file,
tmpf.folder_position as folder_position,
COUNT(tmp.photo_id) as countpics
FROM tbl_member_photo_folders tmpf
INNER JOIN tbl_member_photos tmp on tmp.folder_id = tmpf.folder_id
WHERE (tmpf.member_id = <cfqueryparam value="#member_id#">)
GROUP BY tmpf.FOLDER_ID,tmpf.FOLDER_NAME,tmpf.thumbnail_url, tmpf.thumbnail_file, tmpf.folder_position
ORDER BY folder_position
Thoughts?