1 Topic by Frank H (edited by Frank H 2005-01-27 15:00)

  • Frank H
  • Former PunBB Developer
  • Offline
  • Registered: 2003-03-16
  • Posts: 1,423

Topic: Best way to search for orphans? (is it possible to do in SQL?)

I want to fetch all id's of the attachments from where the post_id cell references to a post that no longer exist.

I have earlier done this in php and several queries, but I'm wondering if it's possible to write one SQL query to do this?

(I figured it'll probably be an extreme gain in processign speed if SQL will be able to handle it entierly)

(i.e. I want to fetch af.id where af.post_id isn't in any post.id)

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: Best way to search for orphans? (is it possible to do in SQL?)

SELECT a.id FROM attachments AS a LEFT JOIN posts AS p ON p.id=a.post_id WHERE p.id IS NULL

That should work. I haven't tried it, so dont' cut me head of if it doesn't smile

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Rickard's Website

  • Frank H
  • Former PunBB Developer
  • Offline
  • Registered: 2003-03-16
  • Posts: 1,423

Re: Best way to search for orphans? (is it possible to do in SQL?)

Thanks! smile

Tested it in myphpadmin, and it seems to work smile

SELECT af.id FROM `attach_12_attach_2_files` AS af LEFT JOIN `attach_12_posts` AS p ON p.id=af.post_id WHERE p.id IS NULL

returned 3 attachment id's ... and IIRC that's how many attachments I made to be orphans smile