RE: [PHP] mysql fun! JOIN me ...

The syntax to do this in Transact SQL would be, Delete From notes where location in (select rowid from locations where map = @MapID) Delete From Locations Where Map = @MapID Delete From Maps Where RowID = @MapID I dont imagine that MySQL would be that different, although I have a nagging feeling that MySQL doesnt suppport nested queries. BTW, "@MapID" in the above code is a variable with ID of the map you want deleted. Jason -----Original Message----- From: zerosumzero@yahoo.com [mailto:zerosumzero@yahoo.com] Sent: 05 July 1979 22:38 To: php-general@lists.php.net Subject: [PHP] mysql fun! JOIN me ... Question 1: is there a good explination online of how to use the JOIN function in mysql? I've looked at the documentation: http://www.mysql.com/doc/J/O/JOIN.html but it's to confusing. It doesn't exactly what you are joining or why you might want to do that ... I think JOIN is what I need, here is the problem: So I have some tables: create table maps ( rowid INT(10) AUTO_INCREMENT PRIMARY KEY NOT NULL ); create table locations ( map INT(10), #map rowid rowid INT(10) AUTO_INCREMENT PRIMARY KEY NOT NULL ); create table notes ( location INT(10), ## location rowid rowid INT(10) AUTO_INCREMENT PRIMARY KEY NOT NULL ); Each map has many locations and each location has many notes ... I want to delete the map, all of it's locations and all of the locations notes. clearing the maps and locations is easy: $sql = "DELETE FROM maps WHERE (rowid='$mapid')"; $result = mysql_query($sql); $sql = "DELETE FROM locations WHERE (map='$mapid')"; $result = mysql_query($sql); but how to get rid of those notes? I'm thinking of sending all of the location rowid(s) to an array then using that array to delete the notes ... but I think this JOIN thing might let me do it all with one query. Can any of you kind folks help me here? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net For additional commands, e-mail: php-general-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net