2

We have an ASP.NET application with SQL Server 2008 back end. We have the following setup in our SpecimenEvents table:

EventID SpecimenID  EventType
      1        101        A       
      2        102        A
      3        103        A
      4        101        B
      5        103        B
      6        101        C

Given a list of SpecimenIDs as input -- how would you write a query to return just those EventType(s) COMMON to all SpecimenIDs in the input list? For example:

a SpecimenID input list of (101,102,103) should return 'A'

a SpecimenID input list of (101) should return 'A','B','C'

a SpecimenID input list of (101,103) should return 'A', 'B' ...

Kermit
  • 33,827
  • 13
  • 85
  • 121
user1132464
  • 21
  • 2

2 Answers2

2
select distinct EventType
from (
    select EventType, count(distinct SpecimenID) as SpecimenCount
    from SpecimenEvents
    where SpecimenID in (101,103)
    group by EventType
    having count(distinct SpecimenID) = 2 -- Make this match the list length
) x
Gabe
  • 5,113
  • 11
  • 55
  • 88
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 1
    Extra `)` after `IN` and need a column name for `COUNT`, otherwise +1. – Kermit Oct 23 '12 at 19:12
  • Thanks for your braintime, guys -- this might do it. Is there a way to do this with SQL Server CTEs without having to input the length of the input list? – user1132464 Oct 23 '12 at 19:24
  • @user1132464 I'm pretty sure you can't do it without the length. I am reluctant to say "impossible", but I've never seen it done and I can't think how you could do it. – Bohemian Oct 23 '12 at 19:39
  • 6
    It has nothing to do with aggregated columns (try your code with `EventType + ''`). It has to do with guaranteeing a contract about what columns exist in the derived table. Why would you ever want to write a query without proper aliases on all columns, even if right now you aren't using one of them? – Aaron Bertrand Oct 23 '12 at 19:52
  • @Bohemian: In about 13 different ways (some with length, some without and several that require different number of joins depending on the length of the list): [How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation) – ypercubeᵀᴹ Oct 23 '12 at 19:58
  • @AaronBertrand Why would I want columns that don't have a proper name? Oh, that's easy to answer! When they are transient and I don't care about their names... just like in this (inner/aliased) query! – Bohemian Oct 23 '12 at 20:04
  • @ypercube I count any query where you have to mention the parameters, or something about the parameters, more than once as "you can't do it". All queries in that other question do this. – Bohemian Oct 23 '12 at 20:07
  • 3
    @Bohemian if you don't like using the alias on transient columns, then just drop using the column all together -- http://sqlfiddle.com/#!3/168a1/2 – Taryn Oct 23 '12 at 20:07
  • @bluefeet cool - I like that much more (didn't know the SQL Server supported having without selecting). Re dislike, no other database (I know) requires an alias - it's just code clutter. – Bohemian Oct 23 '12 at 20:10
  • @Bohemian you're getting pretty worked up over something that's relatively insignificant. your initial comment was not constructive at all. fine, you don't like sql server --- thanks for answering the question, though. – swasheck Oct 23 '12 at 20:14
  • @Bohemian: All except my lucky number 7 :) – ypercubeᵀᴹ Oct 23 '12 at 20:15
  • @Bohemian then why are you including it in the output list? – Aaron Bertrand Oct 23 '12 at 20:40
  • 5
    SQL Server is so hard! It has syntax rules! Time to switch platforms! – Aaron Bertrand Oct 23 '12 at 20:41
0

Try This Code

    declare @1 varchar(10),@2 varchar(10), @3 varchar(10), @sql nvarchar(4000)
set @1='101'
set @2= '103'
set @3= null

set @sql =''
if @1 is not null set @sql = @sql+'
intersect
SELECT [EventType] from SpecimenEvents where [SpecimenID] = ' + @1
if @2 is not null set @sql = @sql+'
intersect
SELECT [EventType] from SpecimenEvents where [SpecimenID] = ' + @2
if @3 is not null set @sql = @sql+'
intersect
SELECT [EventType] from SpecimenEvents where [SpecimenID] = ' + @3
if len(@sql)>13 
begin
set @sql = substring(@sql,14,4000)
execute sp_executesql @sql
end

Amit Patel

Amit Patel
  • 1