SQL Join Question -When 2nd table has no matching entries

[TechBoyJK]

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

	[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

	[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

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?

 

[Crusty]

http://stackoverflow.com/questions/38549/sql-difference-between-inner-and-outer-join

 

[KLin]

LEFT JOIN instead of INNER JOIN. That will return all folders regardless of matching photos in child table.

Google is your friend.

 

[TechBoyJK]

LEFT JOIN instead of INNER JOIN. That will return all folders regardless of matching photos in child table.

Google is your friend.

brah! Thanks! It works!

 

[Crusty]

brah! Thanks! It works!

Do you know why it works though? That's what you should be after....

 

[KIAman]

Some material to read for why a left (also known as left outer) join works.

http://en.wikipedia.org/wiki/Set_(mathematics)

 

[TechBoyJK]

Do you know why it works though? That's what you should be after....

Yes I do.. Not sure why it didn't cross my mind though.

 

[ringtail]

Paul DuBois gives a real clear explanation for "Finding Rows with No Match in Another Table" in MySQL Cookbook ch 12.6, which you can read right here.

As Dubois says, " if a left table row has no match in the right table, a LEFT JOIN still produces a row—one in which all the columns from the right table are set to NULL. This means you can find values that are missing from the right table by looking for NULL."

I'll just simplify what he explains for you here:

SELECT what you want to see
LEFT JOIN other table name
ON (leftTable.colName = rightTable.colName)
WHERE rightTable.colName IS NULL;

 

[KIAman]

Paul DuBois gives a real clear explanation for "Finding Rows with No Match in Another Table" in MySQL Cookbook ch 12.6, which you can read right here.

As Dubois says, " if a left table row has no match in the right table, a LEFT JOIN still produces a row—one in which all the columns from the right table are set to NULL. This means you can find values that are missing from the right table by looking for NULL."

I'll just simplify what he explains for you here:

SELECT what you want to see
LEFT JOIN other table name
ON (leftTable.colName = rightTable.colName)
WHERE rightTable.colName IS NULL;

And a cool trick with outer joins is that you can completely remove IN and NOT IN using outer joins like this.

For some DBMS, that increases performance tremendously.

 

[KLin]

And a cool trick with outer joins is that you can completely remove IN and NOT IN using outer joins like this.

For some DBMS, that increases performance tremendously.

It definitely does in a sqlce database running on a mobile device.