We help IT Professionals succeed at work.
Get Started

Optimalisation of double LEFT JOIN MySQL query

thijs321
thijs321 asked
on
800 Views
Last Modified: 2012-05-05
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

Comment
Watch Question
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
Unlock 2 Answers and 6 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE