A query on two joined tables

Question

I have 2 tables. The first table is feeds and (feeds.ID) is the primary key. The second table is items. feeds.ID is related to items.feed_ID, so with a simple inner join I can extract the data of each feed.

Now, I deleted some of the feeds but didn't delete them from the items table. I want to find the items which have no resource on the feeds table. On the other hand, I want to find the useless items. What is the correct query?

I have tried this:

select * from items where feed_id NOT IN 
    (select id from feeds 
        inner join items on feeds.id=items.feed_id group by feed_id) group by feed_id

but it shows an error.

Answer 1

Try this query:

select * from items where feed_id NOT IN (select id from feeds)

Also set foriegn key relationship with delete cascade. So it will get deleted from dependent tables as well.

Answer 2

To view the orphaned records with a join:

select * 
  from Items i
  left join  Feeds f on f.ID = i.Feed_ID
 where f.ID IS NULL

You can also delete the orphans using the same logic:

delete i
  from Items i
  left join  Feeds f on f.ID = i.Feed_ID
 where f.ID IS NULL

To prevent this problem in the future, you should add a foreign key constraint to Items.Feed_ID referencing Feed.ID. This would have generated an error when you tried to delete the parent Feed records without first deleting the child Item records. A cascading delete would work as well, as Praveen suggests.

Answer 3

One note about foreign keys: if you are using MySQL, you should use the InnoDB storage engine instead of the MyISAM engine (the typical default). If you are using InnoDB and you create a foreign key index/constraint, it will be enforced by the system - this is where you would want to use the delete cascade instruction in the foreign key constraint code (alter constraint fk_whatever ...).

With MyISAM, the system has no problem creating Foreign Key constraints and gives no error, but in reality it does zero good because MySQL's MyISAM engine doesn't enforce the constraint. This is a serious problem if you are under the impression it should be enforcing those constraints.