Left Join/Ifnull problem

[rookiecoder]

Hi, I thought I had a simple Left Join/Ifnull solution to my problem but it is taking me ages to figure out . As of now I've the following query from where I need to extract the employee's name and latest rank as per his last contract

SELECT employee.id,employee.name,rank_name.name
FROM employee,contract,rank_name
WHERE contract.employee_id=employee.id AND contract.rank=rank_name.rank
AND contract.start_date=(SELECT MAX(contract.start_date) FROM contract WHERE contract.employee_id=employee.id)
ORDER BY employee.name;

rank_name is an enumeration of all the ranks. So the above seems to be working fine , except that employees who have no contract records (hence no ranks) are not retrieved. What would be the best way to use Left Join ? From the few examples on web, I have only been able to see Left Join being used on a pair of tables, however here I have 3 tables

[guelphdad]

Get out of the habit of using comma join syntax. It is easier to spot errors that way.

Rewrite your queries like this:

SELECT
employee.id,
employee.name,
rank_name.name
FROM
employee
LEFT JOIN
contract
ON
contract.employee_id=employee.id
AND
contract.start_date=(SELECT MAX(contract.start_date) FROM contract WHERE contract.employee_id=employee.id)
INNER JOIN
rank_name
ON
contract.rank=rank_name.rank
ORDER BY employee.name;


You can now see exactly which columns your tables are joined on.

Also notice how the subquery is in the join itself using the ON clause.

If you have a LEFT JOIN but put a condition on the right hand table, it belongs in the join and not the where clause. Moving it to the where clause changes your query to an INNER JOIN. This is because you are saying give me all matching and unmatching rows in the query, but now discard any of the non-matching rows that don't meet the where clause. Leaving that clause in the join itself returns matching and unmatching (in your case, those without contracts) rows.

[Old Pedant]

And by the by, you can *NOT* specify a LEFT or RIGHT join using the comma-join syntax. Your original query *IS* a pair of INNER joins, period. That's the only kind of join you can get with code such as your original.