How can I query to do this?

Question

I have 2 tables, in feeds I have my feeds and (feeds.ID) is the primary key. second table is items. and feeds.ID is realted to items.feed_ID, so with a simple inner join i can extract the data of each feed. but now i have deleted some of the feeds but i didnt delete them in items table. i want to find the items which have no resource on feeds table. on the other hand i want to find the useless items. what is the query? i have tried this: but it shows error

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

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.