How to - DELETE from one table if id not present in second

ericg212

I need some help in writing the query or queries that will delete a "Sport" from table one if their are no "Leagues" related to it in table two. The sport id in table one is carried over in table two as sportid

DELETE if no leagues assigned to sport / otherwise you must first delete the leagues.

$deleteSport = "DELETE FROM Sports WHERE Sports.id='$sport_id'";
$result = mysql_query( $deleteSport );

I am able to just delete the sport, but I can't figure out the rest

volleytotal_ch

you can't do it with just 1 query (unless you got mySQL 4 but its still beta)

I'd do it this way:

$query = "SELECT count(l.somerow) FROM Leages l, Sports s WHERE l.sport=s.id AND s.id='$sport_id'" // Dunno how you linked the tables ;)
$res = mysql_query($query,$conn) or die ("Query failed!");

if(mysql_result($res,0)==0)
{
  // There is no row in leages corresponding to this sport
  // You can delete this sport
  $query = "DELETE FROM Sports WHERE id='$sport_id'";
  .....
}
else
{
  // At least one 'Leage' was found which belongs to this sport
  // Cant delete the sport (or delete both leage and sports...)
}

hth

[deleted]

Don't you just love mysql's lack of cascading deletes? 🙂

In a real database you would define the sportid relation between the two tables as 'ON DELETE CASCADE'. Then if you delete an item fro the first table, all related items are also removed from the second table automatically.

In the latest versions of mysql you can at least define some sort of Primary-key / Foreign-key relation which will prevent you from removing records from the first table that are still being referenced from the second table.

As for the solution, remember that you must check whether every delete from the second table was executed succesfully before you actually delete the record from the first table.

MySQL does not do any integrety checks, you must do them all in your application.

ericg212

Thanks I got it working using your code. This is what it looks like:

$message="";
if ( isset( $actionflag ) &&
	$actionflag == "deleteSport" && isset( $sport_id ) )
	{
	$listLeagues = "SELECT COUNT(Leagues.sportid) FROM Leagues, Sports
		WHERE Leagues.sportid=Sports.id AND Sports.id='$sport_id'";
	$result = mysql_query( $listLeagues, $connect );
	if ( ! $result )
		die ("listLeagues error: ".mysql_error());
	if (mysql_result($result,0)==0)
		{
		$deleteSport = "DELETE FROM Sports WHERE Sports.id='$sport_id'";
		$result = mysql_query( $deleteSport,$connect );
		}
	else
		{
		$message .= "At least one league was found which belongs to this sport!<br>
		 You must first delete all related leagues.";
		}
	}