I have 4 MySQL tables:
jobs (id, title, branchId)
contains jobs and the branch where they are part of
oldJobs (id, title, branchId)
same as jobs, except contains old jobs which are not open anymore
branches (id, name)
contains all branches
applies (id, jobId)
contains applies with their corresponding job id
Now I need to get the number of applies that were received per job and the corresponding branch.
If I only search for applies in the `jobs` table and forget about the `oldJobs`, then I use the query below.
How to change this query for including the `oldJobs`?
(The first that comes into my mind is to add another
LEFT JOIN for `oldJobs`, but then I don't know how to include to corresponding branch, because in the query below
the branch is LEFT JOINed on `jobs` and not `oldJobs`.)
applies AS a
jobs AS j
a.jobId = j.id
branches AS b
b.id = j.branchId