0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for practitioners of the art and science of signal, image and video processing

-- will hold our selected tags
create table #selectedtags(value nvarchar(35) collate SQL_Latin1_General_CP1_CS_AS)

-- fill selectedtags based on the ; delimited tags 
insert into
#selectedtags
select rtrim(ltrim(value))
from string_split(##tags:string?winforms;linq##, ';')

-- select posts
select p.id as [Post Link]
     , p.creationdate
     , p.score
     , p.tags
from posts p
inner join posttags pt 
        on pt.postid = p.id  -- for each tag per post a row 
inner join tags t 
        on t.id = pt.tagid  -- which tag
inner join #selectedtags st 
        on st.value = t.tagname -- select the tags
-- check if the post doesn't have more tags
where (select count(*) 
       from posttags pc 
       where pc.postid = p.id and (p.answercount = 0 or p.acceptedanswerid is null)) = (select count(*) 
                                  from #selectedtags)
and closeddate is not null
-- have the exact count of tags                                 
group by p.id 
     , p.creationdate
     , p.score
     , p.tags
having count(*) = (select count(*) 
                   from #selectedtags)

fork query

edited aug 19 19

created aug 19 19 anonymous