Avatar of thijs321
thijs321

asked on 

Optimalisation of double LEFT JOIN MySQL query

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.

Question:
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`.)
SELECT
				a.id,
				j.title,
				b.name
			FROM
				applies AS a
 
			LEFT JOIN
				jobs AS j
			ON
				a.jobId = j.id
 
			LEFT JOIN
				branches AS b
			ON
				b.id = j.branchId

Open in new window

SQL

Avatar of undefined
Last Comment
Mark Wills

8/22/2022 - Mon