I feel so ashamed. 2

[HestonJames]

This has to be the simplest query I've not been able to get working, I hang my head on this and hope you wont judge me ;-)

I have this query:

Delete From	ThinkTankGroup
Where		ThinkTankGroup_ID Not In (
									Select	ThinkTankGroup_ID
									From	ThinkTank
									)

Now, this is meant to remove all records from ThinkTankGroup table which are not referenced from the ThinkTank table, however, it doesn't ever seem to delete anything :-s

So, for instance, if:

Select	ThinkTankGroup_ID
From	ThinkTank

returns a list like this:

1
NULL
NULL
NULL
1
1
1
NULL
1
NULL

Then running that first query wont delete rows with a ThinkTankGroup_ID of 3 or 4 or 5 etc, when in my mind, it should!??

Any ideas guys? I feel like a dork for asking but its gotten me confused and angry this morning

Heston

 

[SimonSellick]

Heston,

The clue is in your SELECT list results - the null entries mean that the NOT IN never returns true (null is just null). If you were to replace the null entries in ThankTank.ThankTankGroupId with 0 (for example), then it should give you the results you expect. Probably.

Hope this helps.

Simon.

 

[HestonJames]

Spot On!

Used an IS NOT NULL on the subquery and it worked as expected! good man, thank you very much.

Heston

 

[sasadear]

so wise

http://www.dearok.com