MYSQL/PHP delete from two tables 1

[MikeWire]

Hi all, can someone help me with my query please? I have done some searching and can't seem to find an answer to my query problem. I need a query that will delete one vehicle(v_id) AND all service(s_id) records associated with the vehicle. The problem is that my existing query only works when there is one or more than one service record associated with the vehicle deleted...it will not work if there is one vehicle and no service records.

Here is my query:

DELETE vehicles, service FROM vehicles, service WHERE vehicles.v_id = ".$delete_vehicle." AND service.v_id = ".$delete_vehicle."

I am using PHP if that helps out with the solution.

I have two tables - the relationship is one vehicle(v_id) to none, one, or more than one service record(s_id):

service
------------
[s_id](index)
v_id
s_type
s_date
-----------
and
vehicles
------------
[v_id](index)
v_make
v_model

I hope I explained everything, but please post if you have questions - thanks in advance!

MikeWire

 

[Kozusnik]

Is there more to the WHERE statement? If not, just do two separate DELETE statements.

$sql = 'DELETE FROM service WHERE service.v_id = ".$delete_vehicle."';
mysql_query($sql);
$sql = 'DELETE FROM vehicles WHERE vehicles.v_id = ".$delete_vehicle."';
mysql_query($sql);

Mark

 

[MikeWire]

Thanks Kozusnik -
There's not much more to that where statement, but the $delete_vehicle var is being put into an array (checkbox array) then looped through a while statement for (if) multiple deletes are selected.

I suppose that would work, but I was thinking there was a more efficient way to run the query, but I'm not too advanced with SQL

If there's a more efficient way I would like to know, but I am going to try out your suggestion - thanks!

 

[MikeWire]

This does work - thank you!