Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unanswered: 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.
    Code:
    INSERT INTO @TempTable
    SELECT	a.*
    FROM	DataTable 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.
    Code:
    INSERT INTO @TempTable
    SELECT	a.*
    FROM	DataTable a
    JOIN	@TempTable aa
    	ON a.FK_COLUMN = aa.ID
    WHERE a.ID <> aa.ID -- Exclude records that already exist
    
    INSERT INTO @TempTable
    SELECT	a.*
    FROM	DataTable 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

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I've tested the first version of your code and it worked correctly. Are you sure you do not miss something else?
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Mar 2010
    Posts
    2
    This issue has now been solved. The problem was that "SELECT ID FROM @TempTable" was returning some NULL values. When you use NOT IN with a NULL value you get no results. All I needed to do was write it "SELECT ID FROM @TempTable WHERE ID IS NOT NULL".

Posting Permissions

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