create table #selectedtags( id int primary key , value nv...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- will hold our selected tags
create table #selectedtags( id int primary key
                          , value nvarchar(35) collate SQL_Latin1_General_CP1_CS_AS)

-- fill selectedtags based on the ; delimited tags 
insert into
#selectedtags
select t.id, rtrim(ltrim(value))
from string_split(##tags:string?winforms;linq##, ';') v
inner join tags t on t.tagname = rtrim(ltrim(value))

select top 1000 id as [Post Link]
     , creationdate
     , score
     , tags
from
(
select p.id 
     , 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 #selectedtags st 
        on st.id = pt.tagid -- select the tags
-- check if the post doesn't have more tags
where closeddate is not null
and 
( select count(*) 
  from posttags pc 
  where pc.postid = p.id) = 1

union 
-- select posts
select p.id 
     , 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 #selectedtags st 
        on st.id = pt.tagid -- select the tags
-- check if the post doesn't have more tags
where closeddate is not null
and (select count(*) 
     from posttags pc 
     where pc.postid = p.id) = (select count(*) 
                   from #selectedtags)
-- have the exact count of tags                                 
group by p.id 
     , p.creationdate
     , p.score
     , p.tags
having count(*) = (select count(*) 
                   from #selectedtags)
) data
order by  creationdate desc

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results