selecting all the records that aren't a FK somewhere

Spudhead · #1 (**permalink**) 06-16-09, 07:27

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?

mike_bike_kite · #2 (**permalink**) 06-16-09, 07:37

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

Spudhead · #3 (**permalink**) 06-16-09, 07:48

Aha. Yes, that would be it

Thank you

Internet Services

Web Development

Digital Marketing

Consumer Tech