Sign in to follow this  
Thevenin

Finding holes in a MySQL table.

Recommended Posts

I'm having a bit of difficulty implementing a black hole remover using just SQL. What I'm trying to do is remove all entires in Table(A) that have an ID that isn't found in Table(B). Although as simple as this appears, I'm finding it really quite tricky; the last SQL command I experimented with selected 287412 rows >.<!

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
how about posting your SQL statement, preferably with a short description about your db/table layout?

Share this post


Link to post
Share on other sites
Quote:
Original post by Anonymous Poster
how about posting your SQL statement, preferably with a short description about your db/table layout?


I would if I thought it was relevent to the question. Nevertheless, here is a pictorial representation of the problem.



I need an SQL command that will select 'Jasper' from the "employeee" table because Jasper's DepartmentID was not found in table "Department".

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
This kind of thing is normally handled at the database level by declaring foreign keys in the tables. You could in the employee table make a foreign key that regerences department ID, and then place appropriate constraints, such as making a delete of a department fail on the database level if there are employees left, or automatically delete all employees from the table when a department is deleted. All depending on what is the most appropriate for the situation.

Share this post


Link to post
Share on other sites
Or
SELECT * FROM a WHERE a.ID NOT IN (SELECT ID FROM b)
(at least I think that should do the same, in a possibly clearer way). But foreign keys are good, because you can be certain that you'll never to use that kind of cleanup query (unless you've already got a broken inconsistent database that needs to be cleaned up before adding the foreign keys in the first place [smile]). (But be careful when using MySQL because it silently ignores foreign key constraints on certain table types.)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this