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.
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:
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, (
WHERE fileName= t1.fileName AND jobID = 'job01'
) AS TotalProperties
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
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
LEFT JOIN t2 ON t1.jobID = t2.jobID AND t1.fileName = t2.fileName
WHERE t1.jobID = 'job01'
GROUP BY t1.fileName ASC