CODEWITHSUNDEEP
mysql
syrkull
syrkull

Reputation: 2344

database maintenance by removing unnecessary information

I have a comic website with two databases. first table is to store the comic information (number of the issue, the author and so on). the other table is used to store the links for the pictures of all issues.

Here is a sample

table_meta

chapter_id    |     chapter_name    |   chapter_author 
   123        |       whatever!!    |      someone     
   987        |       whatever!!2   |      someone

table_pictures

chapter_id    |     picture_num    |   picture_ext
   123        |       001          |      jpg
   123        |       002          |      jpg

as you can see chapter_id 987 does not have anything in the table_pictures table. I want a mysql query that delete all this unnecessary information. Here is what I came up with:

SELECT * FROM `table_meta`
LEFT JOIN `table_pictures` ON table_meta.chapter_id = table_pictures.chapter_id

This will link the two databases together. but how can I delete anything that is not linked?

Upvotes: 0

Views: 79

Answers (3)

gtgaxiola
gtgaxiola

Reputation: 9331

DELETE FROM table_meta WHERE chapter_id NOT IN(
    SELECT chapter_id FROM table_pictures 
      WHERE table_meta.chapter_id = table_pictures.chapter_id
);

Upvotes: 1

Bjoern
Bjoern

Reputation: 16314

Here's an example how to negate your LEFT JOIN and use it for deleting the data in table_meta:

DELETE table_meta
FROM table_meta
LEFT JOIN table_pictures ON table_meta.chapter_id = table_pictures.chapter_id
WHERE ISNULL(table_pictures.chapter_id);

Upvotes: 2

tadman
tadman

Reputation: 211740

When you do a LEFT JOIN it's possible that the joined table will have NULL for all data:

SELECT ... WHERE table_pictures.chapter_id IS NULL

So you can do this to find all those where the link doesn't work, or as an inverse, RIGHT JOIN and select all those not in the list with a WHERE ... NOT IN (SELECT ...)

Upvotes: 2

Related Questions