Psycho Posted November 3, 2006 Share Posted November 3, 2006 I have three table with the following pertinent fields:[movies] - movie_id - movie_name[genres] - genre_id - genre_name[movie_genres] - movie_id - genre_idNow, if I have a genre that is associated with only one movie and then modify the movie to no longer be associated with the genre, I want to run a query to delete the orphan genre. In other words I need to delete records from the 'genres' table where the 'genre_id' does not exist in the 'movies_genres' table. How would I create that query?$sql = "DELETE FROM genres WHERE ????"; Quote Link to comment https://forums.phpfreaks.com/topic/26068-query-to-delete-orphan-records-solved/ Share on other sites More sharing options...
shoz Posted November 3, 2006 Share Posted November 3, 2006 [code]DELETEgenresFROMgenresLEFT JOINmovie_genresONgenres.genre_id = movie_genres.genre_idWHEREmovie_genres.genre_id IS NULL[/code]http://dev.mysql.com/doc/refman/5.0/en/join.htmlhttp://dev.mysql.com/doc/refman/5.0/en/delete.html Quote Link to comment https://forums.phpfreaks.com/topic/26068-query-to-delete-orphan-records-solved/#findComment-119169 Share on other sites More sharing options...
Psycho Posted November 3, 2006 Author Share Posted November 3, 2006 Thank you! Quote Link to comment https://forums.phpfreaks.com/topic/26068-query-to-delete-orphan-records-solved/#findComment-119266 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.