1

I have a table like below

ID   Value
----------
1    A
2    B
3    C
3    C
4    A
4    D
5    A
5    C
5    D

I want a query or stored procedure that will identify If there is already a combination exist if I try to insert the same kind of value combination. eg: If I am trying to insert 

6   A
6   D

will let me know that there is already the same combination exist with id 4. is it possible in MSSQL?

Few Comments: When I am trying to insert the new values the Id will be new, so we can't have a search on the table for duplicate rows with id, value combination. I need a way to search for the duplicate values with the same id value combination.

In the above sample when I am trying to insert

6   A
6   D

It will go and search in the table if there is any rows exist for values with A and D having the same ID in the above table there is an ID 4 so it should let me know that there is a duplicate entry when I try to insert this.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
Saanch
  • 1,814
  • 1
  • 24
  • 38

5 Answers5

2

This will give you the ID's from @T that already have the combination of values provided in @NewValues.

declare @T table (ID int, Value char(1))
insert into @T values
(1,    'A'),(2,    'B'),(3,    'C'),(3,    'C'),
(4,    'A'),(4,    'D'),(5,    'A'),(5,    'C'),
(5,    'D')

declare @NewValues table(ID int, Value char(1))
insert into @NewValues values (6,    'A'), (6,    'D')

select T.ID
from @T as T
  inner join @NewValues as N
    on T.Value = N.Value
group by T.ID
having count(*) = (select count(*) from @NewValues)

Result:

ID
4
5

If you only want exact matches, meaning that ID=5 would not be returned because it also have one row with Value='C' you can use this instead.

select T.ID
from @T as T
  left outer join @NewValues as N
    on T.Value = N.Value
group by T.ID
having count(N.Value) = (select count(*) from @NewValues) and 
       count(*) = (select count(*) from @NewValues)

I see that you have (3,'C') and (3,'C') in your table. If you want to detect that with the input (6, 'C') and (6, 'C') you need this query.

select T.ID
from @T as T
  left outer join (select distinct Value
                   from @NewValues) as N
    on T.Value = N.Value
group by T.ID
having count(N.Value) = (select count(*) from @NewValues) and 
       count(*) = (select count(*) from @NewValues)

Fill `@NewValues? table with a string split function.

-- Paramenter to SP
declare @ParamID int = 6
declare @ParamValues varchar(100) = 'A,D'

declare @NewValues table(ID int, Value char(1))
insert into @NewValues
select @ParamID, s
from dbo.Split(',', @ParamValues)
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • From how I understood the question, the existing combination(s) should match exactly. For instance, ID 5 cannot match because it contains an 'extra' item 'C', which the new combination doesn't have. So it should probably be full join, like in @Alex Aza's answer. But that's just my understanding. – Andriy M Jun 23 '11 at 06:17
  • @Andriy - Added a second version that I *believe* would take care of that. – Mikael Eriksson Jun 23 '11 at 06:21
  • @Mikael: Yes, if my assumption was correct, your second query should find duplicates correctly. But please take a look at the alternative version with full join that I added to your answer. (Please rollback it if you don't agree.) – Andriy M Jun 23 '11 at 06:37
  • Wow! Thanks for that answer. Can we have this without the newTable like before I insert pass the combination of Values to a stored procedure and return me if there is an ID exist with the same combination. – Saanch Jun 23 '11 at 06:45
  • @Andriy - The full outer join version you added will return ID=1 as well. – Mikael Eriksson Jun 23 '11 at 06:48
  • @SqlBeginner - How do you pass the values to the SP? – Mikael Eriksson Jun 23 '11 at 06:49
  • @SqlBeginner: Maybe you should define a trigger AFTER INSERT on the table and incorporate the logic into the trigger. That way you would just issue plain INSERT instruction, the trigger would check for the duplicates and rollback the insert if they were found. – Andriy M Jun 23 '11 at 06:58
  • I was looking for a Stored procedure called findDuplicate in that In will pass the AttributeIds as string like (6, "A, C, D") then it will go and check in the table is there any same combination ACD for any ID then report me with the Sequence which already exist. Also let say If I am trying to update an ID with new value or change an existing combination that also findout the new combination and report me the existing ID if any. – Saanch Jun 23 '11 at 07:30
  • @SqlBeginner - Use a string split function like this one and insert the values to @NewValues. http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor/314917#314917 – Mikael Eriksson Jun 23 '11 at 07:48
0

Yes, it is possible. One way to do it is to define a primary key as (ID,Value). That will throw an exception every time someone tries to insert an existing (ID,Value) tuple.

rafalotufo
  • 3,862
  • 4
  • 25
  • 28
0

You Should use the MERGE command see here http://pratchev.blogspot.com/2008/03/upsert-and-more-with-merge.html

Daveo
  • 19,018
  • 10
  • 48
  • 71
0

Before you insert, query all the letters that are combined with that id. Then add the letter you want to insert to that list and then check for every Id if there is an Id that has a relation with all the letters in the list. If there is at least one, then notify the user.

zad
  • 3,355
  • 2
  • 24
  • 25
0

The concept is universal quantification, the relational operator is called division, popularly known as "the supplier who supplies all parts".

Because you don't consider ID = 5 to represent a duplicate, you should be looking at exact division (i.e. without a remainder) and an empty divisior is probably not an issue in your case.

Useful article: On Making Relational Division Comprehensible

onedaywhen
  • 55,269
  • 12
  • 100
  • 138