Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This will give you the ID's from @T that already have the combination of values provided in @NewValues.</p> <pre><code>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) </code></pre> <p>Result:</p> <pre><code>ID 4 5 </code></pre> <p>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.</p> <pre><code>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) </code></pre> <p>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.</p> <pre><code>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) </code></pre> <p>Fill `@NewValues? table with a string split function.</p> <pre><code>-- 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) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POfindout duplicate rows in a table while inserting
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMikael Eriksson
    1. USMikael Eriksson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POfindout duplicate rows in a table while inserting
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COFrom 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.
      singulars
      1. PO
      1. USAndriy M
    2. CO@Andriy - Added a second version that I *believe* would take care of that.
      singulars
      1. PO
      1. USMikael Eriksson
    3. CO@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.)
      singulars
      1. PO
      1. USAndriy M
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload