troubleshooting Question

Optimalisation of double LEFT JOIN MySQL query

Avatar of thijs321
thijs321 asked on
SQL
6 Comments2 Solutions802 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros