MYSQL - limiting joins

Question

Hi,

I know this subject has been covered before on here but I need a different result to the other questions.

I need to order some results on institution name and then candidates last name.

SELECT DISTINCT candidates.*, histories.job_title, institutions.name
FROM candidates
JOIN histories ON histories.candidate_id = candidates.id
JOIN institutions ON histories.institution_id = institutions.id
WHERE candidates.id IN (1,3,4,6)
ORDER BY institutions.name, candidates.last_name;

My problem is currently this will return all the candidates joined histories(there current/past employments) but if I add

AND histories.finish_date IS NULL

To the where it won't return candidates who don't have a history or have a finish date set.

Thanks, Alex

Sub Query

SELECT DISTINCT candidates.*, 
   (SELECT institution_id
        FROM histories
        WHERE histories.candidate_id = candidates.id AND histories.finish_date IS NULL
        LIMIT 1) AS job_title       
   FROM candidates
  JOIN institutions ON histories.institution_id = institutions.id
WHERE candidates.id IN (1,3,4,6)

Answer 1

Try using a left join on the histories table:

SELECT DISTINCT candidates.*, histories.job_title, institutions.name
FROM candidates
LEFT JOIN histories ON histories.candidate_id = candidates.id AND histories.finish_date IS NULL
JOIN institutions ON histories.institution_id = institutions.id
WHERE candidates.id IN (1,3,4,6)
ORDER BY institutions.name, candidates.last_name;

I can't verify this, you'll have to do it for me. It should return all the candidates, and their histories (if they have one).