CODEWITHSUNDEEP
mysqlsqldatabase
Jonathan
Jonathan

Reputation: 11321

How can I find orphaned entries between related tables in a database?

I have two mySQL tables, representing documents that were published in particular journals: 

Documents: 

ID Title                          Journal
1  Monty: A New Study             6
2  New Discoveries about Python   17
3  An Unpublished Document        NULL 

Journals: 

ID Title 
6  Journal of Foo
10 Orphans Weekly 
17 Journal of Bar
99 Journal of Orphans

Here, the document "Monty: A New Study" was published in Journal of Foo, and "New Discoveries about Python" was published in the illustrious journal called Journal of Bar.

The problem is, for whatever reason there are journal titles that don't have associated documents, namely #10 and #99. I want to delete all the entries of journals that don't have associated documents. I want to do something like: 

delete from Journals where id is not one of (select journal from documents where journal is not null)

But I'm new to mySQL and got stuck on this.

Upvotes: 0

Views: 288

Answers (2)

dursun
dursun

Reputation: 1846

you can do it as follows;

delete from Journals where id not in (SELECT Journal from Documents)

Upvotes: 1

Ian Atkin
Ian Atkin

Reputation: 6356

DELETE FROM `Journals` AS j WHERE j.ID NOT EXISTS(SELECT Journal FROM `Documents`);

or...

DELETE FROM `Journals` AS j WHERE j.ID NOT IN (SELECT Journal FROM `Documents`);

Upvotes: 1

Related Questions