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
Permalink
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 😉
Permalink
I will give yours a try. I do agree that the Join is faster. just couldn’t figure it our this time
Permalink
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.
Permalink
Also, do you really still need the jobid in the third table since your where clause specifies the jobid that you’re selecting?
Permalink
which third table? I am only using two
Permalink
I meant the result table. It’s not really important, though 🙂
Permalink
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;
Permalink
thanks i will try this
Permalink
this worked great i am posting the solution on the original blog