[SOLVED] advanced mysql query

[thefortrees]

Hi all,

 

I need help with a mysql query.

 

Explanation of problem -

 

I have two tables, vehicles and vehicle_photos.

vehicles

---------

vehicles_id

....

....

vehicles_photo_datein

 

and

 

vehicle_photos

---------

vehicle_photos_id

vehicles_vehicles_id

....

....

photo_datein

 

There are quite a few rows in the vehicles table that have null values for vehicles_photo_datein.

I need to take the most recent datein from vehicle_photos that corresponds to vehicles_id in vehicle with null vehicles_photo_datein.

 

I have tried many combinations of queries and can't get it right.

Here is what I started with which doesn't work(and will explain what I want better than a paragraph could)

 

update vehicles v, vehicle_photos vp set v.vehicles_photo_datein = 
(select max(vp.photo_datein) from vehicles v, vehicle_photos vp 
where v.vehicles_photo_datein is null and v.vehicles_id = vp.vehicles_vehicles_id) 
where v.vehiclephoto_datein is null;

 

Thanks!

[thefortrees]

Solution:

 

update vehicles set vehicles.vehicles_photo_datein =  
          (select max(photo.datein) from vehicle_photos 
           where vehicles.vehicles_photo_datein is null 
           and vehicles.vehicles_id = vehicle_photos.vehicles_id) 
where vehicles.vehicles_photo_datein is null;