Query questions with some tags but not others

1

Please login or register to vote for this query.

(click on this box to dismiss)

Chess

Q&A for serious players and enthusiasts of chess

DECLARE @tagsin nvarchar(250) = '##IncludeTags##'
DECLARE @tagsex nvarchar(250) = '##ExcludeTags##'
DECLARE @minscore int = ##MinimumScore##
DECLARE @minviews int = ##MinViewCount##
DECLARE @maxanswers int = ##MaxAnswerCount##
DECLARE @mincomments int = ##MinCommentCount##
DECLARE @maxcomments int = ##MaxCommentCount##

DECLARE @InTags TABLE (InTag nvarchar(35))
INSERT INTO @InTags
SELECT DISTINCT * FROM STRING_SPLIT(@tagsin, ' ')
UPDATE @InTags SET InTag = '%'+InTag+'%'

DECLARE @ExTags TABLE (ExTag nvarchar(35))
INSERT INTO @ExTags
SELECT DISTINCT * FROM STRING_SPLIT(@tagsex, ' ')
UPDATE @ExTags SET ExTag = '%'+ExTag+'%'

SELECT TOP 5000 Posts.Id as Id,CreationDate,Score,ViewCount,Body,
Tags,OwnerUserId,LastEditorUserId,LastEditDate,LastActivityDate,
Title,CommentCount,FavoriteCount,CommunityOwnedDate
FROM Posts INNER JOIN (
  SELECT Id
  FROM (
    SELECT Id,Tags
    FROM Posts
    WHERE PostTypeId=1
  ) Questions INNER JOIN @InTags
  ON Tags COLLATE SQL_Latin1_General_CP1_CI_AS
  LIKE InTag COLLATE SQL_Latin1_General_CP1_CI_AS
  LEFT JOIN @ExTags
  ON Tags COLLATE SQL_Latin1_General_CP1_CI_AS
  LIKE ExTag COLLATE SQL_Latin1_General_CP1_CI_AS
  WHERE Extag IS NULL
  GROUP BY Id,Tags
  HAVING COUNT(InTag) = (SELECT COUNT(InTag) FROM @InTags)
) T
ON Posts.Id=T.Id
WHERE AnswerCount<=@maxanswers
AND Score>=@minscore
AND ViewCount>=@minviews
AND ClosedDate IS NULL
AND DeletionDate IS NULL
AND CommentCount>=@mincomments
AND CommentCount<=@maxcomments
AND NOT Body LIKE '%Possible Duplicate%'

Enter Parameters

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