One-to-Many Count Parent’s Children in mySQL

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

9 Comments


  1. This is a little late, and I haven’t tested it so there may be some syntax errors, but I think this is what you’re looking for:


    select t1.JOBID, t1.FILENAME, count(t2.FILENAME) as TotalProperties
    from t1
    join t2 on t2.JOBID = t1.JOBID and
    t2.FILENAME = t1.FILENAME
    where t1.JOBID = 'job01';

    I don’t know what your indexes are on, but a join is usually faster than a sub-select. If you’re using MySQL, you can use the “explain” function to figure out which one is faster, yours or mine.

    I’ll take a closer look when I get home; I don’t have a MySQL instance running on my laptop and don’t really want one 😉

    Reply

    1. I will give yours a try. I do agree that the Join is faster. just couldn’t figure it our this time

      Reply

    2. So I tried your way and I got nowhere, the Join only returns One record with count being the total number of records on table 2 .
      i did notice that the execution times were identical in both cases. then again right now my sample set is quite small.

      Reply

  2. Also, do you really still need the jobid in the third table since your where clause specifies the jobid that you’re selecting?

    Reply

    1. which third table? I am only using two

      Reply

      1. I meant the result table. It’s not really important, though 🙂

        Reply

  3. Just for the record SUBSELECT is wrong, JOIN is the right way to do this, it’s actually a LEFT join (in fact it was the reason “LEFT JOIN” was invented) and you need a GROUP BY clause:

    select t1.JOBID, t1.FILENAME, count(t2.FILENAME) as TotalProperties
    from t1
    left join t2 on t2.JOBID = t1.JOBID and
    t2.FILENAME = t1.FILENAME
    where t1.JOBID = ‘job01’
    GROUP BY t1.FILENAME;

    Reply

    1. this worked great i am posting the solution on the original blog

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.