select query

Question

declare @tmp table

(

id INT IDENTITY(1,1),

Summary varchar(500),

type varchar(10),

filename varchar(500)

)

INSERT INTO @tmp

select 'xxx', 'PDF', 'FileName1'

union all

select 'yyy', 'PDF', 'FileName2'

union all

Select 'yyy', 'MSG', 'filename3'

union all

select 'zzz', 'PDF', 'filename4'

union all

select 'zzz', 'PDF', 'filename5'

select * from @tmp

I need to get a result of id 1, 4, 5

if there is any summary that has msg record, that shouldn't be output
TIA

Answer 1

select * 
from   @tmp a
WHERE  NOT EXISTS (SELECT * 
                   FROM   @tmp b
                   WHERE  b.Summary = a.Summary
                     AND  b.type = 'MSG')