Re: [PHP3] OT help with Query

In article <000701bfcf49$3644bd20$0b00a8c0@wk02>, matt@isitgone.com ("Matt Overall") wrote: > Does anyone know how I can run a query to delete all records from a table where there is not corresponding entry on another > table.(in mySQL) > > eg: > > DELETE ALL FROM shopping_cart WHERE shopping_cart.user NOT FOUND IN sessions. If MySQL did subqueries, you'd be damn close: delete from shopping_cart where not user in (select user from sessions) Alas, MySQL doesn't do subqueries last time I checked... I think you could maybe do this: $activesql = "select user from sessions"; $active = mysql_query($activesql) or die(mysql_error()); //Assumption: 0 is not a valid ID //We're using it as a sort of "yeast" //to start of a comma-delimited list: $activeids = "(0"; while (list($id) = mysql_fetch_row($active)){ $activeids .= ", $id"; } $activeids .= ")"; //We now have a comma-delimited list of who not to delete //in $activeids. EG: (3, 5, 12, 47) //Go ahead, make my day: $deletesql = "delete from shopping_cart where user not in $activeids"; $delete = mysql_query($deletesql) or die(mysql_error()); -- Richard Lynch | If this was worth $$$ to you, buy a CD US Customer Support Director | from one of the artists listed here: Zend Technologies USA | http://www.L-I-E.com/artists.htm http://www.zend.com | (this has nothing to do with Zend, duh!)