This is a complicated one to explain but I'll try...
I have a database with the following tables: vehicles, owners, jobs & linkup. Because the cars are only kept by the some of the owners for a matter of weeks, months or years before being sold to a new owner, we need to be able to track who was the owner on such a date. This was the best way I could come up with and to have the linkup table link the owner to the vehicle along with a date and ref number.
I now need to create a list of all particular vehicle model owners, but am struggling to get it to show the most recent owner. It prefers, on the otherhand, to defualt to the first owner - in which case would be our company name and not the present owner.
I have shown my query below and if anyone can this of a better way I'd be very grateful.I have no idea if this makes any sense to you, but will try to explain it more if needs be.PHP Code:
SELECT * FROM jobs
LEFT JOIN linkup ON job_no=l_job_no
LEFT JOIN owners ON o_id=l_o_id
LEFT JOIN vehicles ON v_id=l_v_id
WHERE model ='$model'
GROUP BY chassis
ORDER BY job_no DESC