Use Of A LinkUp Table

[spoondevil]

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.

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 

I have no idea if this makes any sense to you, but will try to explain it more if needs be.

Many Thanks
Ian Gunter

[r937]

please do me a favour, show your table layouts

[spoondevil]

I've taken out all the info which wouldn't be needed for this query, so they stand as...

jobs
 >job_no
 >date_in
 >date_out
 
 owners
 >o_id
 >name_f
 >name_l
 
 vehicles
 >v_id
 >make
 >model
 >chassis
 
 linkup
 >l_job_no
 >l_v_id
 >l_o_id

[r937]

list of all vehicle model owners, showing most recent owner

select v.v_id
     , v.make
     , v.model
     , v.chassis
     , o.o_id
     , o.name_f
     , o.name_l
  from vehicles as v
inner
  join linkup as l
    on l.l_v_id = v.id
inner
  join jobs as j
    on j.job_no = l.l_job_no
   and j.date_in
     = ( select max(date_in)
           from jobs
          where job_no = l.l_job_no )
inner
  join owners as o
    on o.o_id = l.l_o_id

[spoondevil]

Cheers for that, I'll have a blast with it later