Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: selecting all the records that aren't a FK somewhere

    I've got several tables that reference my "Images" table.

    There are a load of records in my Images table that I know can be deleted - they're not referenced by any other table.

    I want to get a list of all those images.

    If I do this (taking an image ID that I suspect to be unused):

    Code:
    SELECT idCards FROM Cards WHERE idImages = 71;
    SELECT idGifts FROM Gifts WHERE idImages = 71;
    SELECT idUsers FROM Users WHERE idImages = 71;
    ...none of those queries return any rows. Which is good.

    But if I then do this:

    Code:
    SELECT idImages FROM Images
    WHERE 1=1
    AND idImages NOT IN (SELECT idImages FROM cg_Cards)
    AND idImages NOT IN (SELECT idImages FROM cg_Gifts)
    AND idImages NOT IN (SELECT idImages FROM cg_Users)
    That returns no rows either. Which, to me, makes no sense. If it's not in any of the rows, it must be in none of the rows.

    Where am I going wrong?

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Spudhead
    Where am I going wrong?
    You just forgot to join the image table id to the other tables id so you end up looking to see if there are any records in the tables rather than that specific id. You also don't need the 1=1 bit. It would also be more efficient to use not exists but if there isn't that much data it doesn't matter too much.
    Code:
    SELECT i.idImages 
    FROM Images i
    WHERE NOT exists (SELECT 1 FROM cg_Cards c where c.idImages = i.idImages)
          AND NOT exists (SELECT 1 FROM cg_Gifts g where g.idImages = i.idImages)
          AND NOT exists (SELECT 1 FROM cg_Users u where u.idImages = i.idImages)
    Mike

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Aha. Yes, that would be it

    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •