Use Of A LinkUp Table
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.
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
please do me a favour, show your table layouts
I've taken out all the info which wouldn't be needed for this query, so they stand as...
list of all vehicle model owners, showing most recent owner
from vehicles as v
join linkup as l
on l.l_v_id = v.id
join jobs as j
on j.job_no = l.l_job_no
= ( select max(date_in)
where job_no = l.l_job_no )
join owners as o
on o.o_id = l.l_o_id
Cheers for that, I'll have a blast with it later :)