So I have two tables. The first one contains a list of images that belong to a job, the second one contains all the different properties of each image.

Parent Table(t1)

fileName	jobID
file1.jpg	job01
file2.jpg	job01

Child Table(t2)

fileName	jobID	OptionName	Value
file1.jpg	job01	contrast	SomeValue
file1.jpg	job01	saturation	SomeValue

There can be zero to many images in the first table and there can be zero to many properties in the second table.

What I wanted to do was to create a query that would return each image name and the total count of all its properties. Something like this:

Result Table(t3)

jobID	fileName	TotalProperties
job01	file1.jpg	2
job01	file2.jpg	0

Now, If there is some other simpler way to do this please post on the comments, but the way I got this to work was with the next query:


SELECT jobID, fileName, (
SELECT COUNT(fileName)
FROM t2
WHERE fileName= t1.fileName AND jobID = 'job01'
) AS TotalProperties
FROM t1
WHERE jobID = 'job01' ORDER BY fileName ASC

As you can see I am using two Select Statements, The outer statement is the one that selects all the fileNames from the job01 on the table t1. The second statement counts all the properties of each image and returns that value as totalProperties. to tell it which items to count feed it the fileName from t1 in its WHERE statement and we also tell it which JobID

I include the job ID in the second SELECT since fileNames can repleat between jobs, if your records are unique you can forgo the JobID

I am sure this can be easily modified to work with any two tables

UPDATE:
Thanks to the commentators I got a better way to do this using a LEFT JOIN. thanks Conners.


SELECT t1.jobID, t1.fileName,count(t2.fileName)AS total
FROM t1
LEFT JOIN t2 ON t1.jobID = t2.jobID AND t1.fileName = t2.fileName
WHERE t1.jobID = 'job01'
GROUP BY t1.fileName ASC