CODEWITHSUNDEEP
sqlsql-serverduplicates
codemonkeyliketab
codemonkeyliketab

Reputation: 320

Determining duplicate data based off of primary key input

I have a SQL Server table A that is 1 to many with table B and C such that 1 entry in A can have many entries in B and C via foreign key.

I am passing the primary key of A into my stored procedure, from that I wish to determine if the input ABC relations has duplicate data to any ABC' relations in the database.

The condition of duplicate is as follows.

If the BC column data of the rows of the input also exist in the database for another A entry, we will call A`, then BC` is a duplicate of BC and A' is a duplicate of A. The procedure should return A' primary key.

So far I am stuck in determining how to do this in a declarative language. My latest attempt has a problem with the columns of B existing in C.

DECLARE @InputAPrimaryKey INT

SELECT B.APrimaryKey
FROM B, C
JOIN(SELECT *
     FROM  B, C
     WHERE B.APrimaryKey = C.APrimaryKey
     AND   B.AprimaryKey = @InputAPrimaryKey) As input
     ON input.Bcolumndata = B.columndata
     AND input.Ccolumndata = C.columndata
     AND ...

This results in red lines under 'As input' which stumps me.

Can anyone give advice on how to approach this problem? Thank you.

Upvotes: 0

Views: 49

Answers (1)

DimaSUN
DimaSUN

Reputation: 921

Try smth like this one ( If I got right your task )

set nocount on

declare @A table ( APrimaryKey int not null )
insert @A values (1),(2),(3)

declare @B table ( APrimaryKey int not null, ColumnData nvarchar(10) )

insert @B values (1,'one'),(1,'four'),(3,'three'),(3,'four')

declare @C table ( APrimaryKey int not null, ColumnData nvarchar(10) )

insert @C values (1,'one'),(1,'two'),(2,'three'),(3,'two')

declare @Ainit integer = 1

select distinct B.APrimaryKey
from @B B
join @C C  on B.APrimaryKey = C.APrimaryKey
where exists  ( select B1.APrimaryKey
                from @B B1
                join @C C1
                on B1.APrimaryKey = C1.APrimaryKey
                and B1.APrimaryKey <> @Ainit

                and B1.ColumnData = B.ColumnData
                and C1.ColumnData = C.ColumnData
                -- Put your additional conditions
)
and  B.APrimaryKey <> @Ainit

Result is 

APrimaryKey
-----------
     3

Upvotes: 1

Related Questions