• 15
• 15
• 11
• 9
• 10

# Finding holes in a MySQL table.

This topic is 4190 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## 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 on other sites
Quote:

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 on other sites
Off the top of my head:
SELECT * FROM a WHERE NOT EXISTS (SELECT * FROM b WHERE b.ID=a.ID)

##### Share on other sites
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.

SELECT * FROM a WHERE a.ID NOT IN (SELECT ID FROM b)