Foreign Key Issue
-
Hi,
I'm having some strange results with T-SQL. I have a table with an ID column. This table has a foreign key reference to itself pointing to the FK_COLUMN.
I have loaded some records into a temp table for archiving and I need to find all children, and also load them into the temp table. However these child records may already be in the temp table. I am having these wierd results:
If I use the following query I get 0 results,although I know there should be.
INSERT INTO @TempTable
SELECTa.*
FROMDataTable a
JOIN@TempTable aa
ON a.FK_COLUMN = aa.ID
WHERE a.ID NOT IN (SELECT ID FROM @TempTable) -- Exclude records that already exist
If I use the following two query's I get too many results, ie I get duplicate ID's in the temp table.
INSERT INTO @TempTable
SELECTa.*
FROMDataTable a
JOIN@TempTable aa
ON a.FK_COLUMN = aa.ID
WHERE a.ID <> aa.ID -- Exclude records that already exist
INSERT INTO @TempTable
SELECTa.*
FROMDataTable a
JOIN@TempTable aa
ON a.FK_COLUMN = aa.ID
AND a.ID <> aa.ID -- Exclude records that already exist
Please can someone spot where I am going wrong.
Thanks
-
Check if this the below code returns any NULL values
SELECT ID FROM @TempTableIf it does your first query will fail..
And posting some sample data along with your table structure will help us help you better.
Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
:-)And i hope you understood why it failed..
If you didn't, have a look at the following link
http://www.sqlservercentral.com/blogs/never_say_never/archive/2010/1/28/in-and-not-in.aspx
Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply