Strange issue with foreign key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Strange issue with foreign key

Author

Topic

Artoo
Starting Member

16 Posts

Posted - 2010-03-18 : 06:27:51

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
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.


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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-18 : 08:20:24

The logic in your select is wrong...you are first doing an JOIN (same as INNER JOIN) to the temp-table, implicitly saying that you want all rows that are present in both tables, and then with the IN statement you want all rows where ID is not in the table variable. This is creating a "conflict". Do something like this instead:

SELECT	a.*
FROM	DataTable a
LEFT OUTER JOIN	@TempTable aa
	ON a.FK_COLUMN = aa.ID
WHERE aa.ID IS NULL

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-18 : 08:24:25

No I don't believe that because joining goes on FK_Column.

No, you're never too old to Yak'n'Roll if you're too young to die.

Artoo
Starting Member

16 Posts

Posted - 2010-03-19 : 02:36:53

The join logic is corrrect as I want to include rows based on FK_COLUMN but exclude rows based on ID.

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".

Thanks for looking.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources