Select posts without specific tag list

1

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

DECLARE @NumPosts INT = ##NumPosts?100##

CREATE TABLE #LanguageTags (
    TagName NVARCHAR(256) COLLATE SQL_Latin1_General_CP1_CS_AS
);

INSERT INTO #LanguageTags (TagName)
VALUES
('<.htaccess>'),
('<abap>'),
('<actionscript>'),
('<actionscript-3>'),
('<ada>'),
('<agda>'),
('<apl>'),
('<applescript>'),
('<arduino>'),
('<arnoldc>'),
('<asm.js>'),
('<assembly>'),
('<autohotkey>'),
('<awk>'),
('<bash>'),
('<basic>'),
('<batch>'),
('<brainfuck>'),
('<c>'),
('<c#>'),
('<c++>'),
('<c++-cli>'),
('<cfml>'),
('<chuck>'),
('<clojure>'),
('<clojurescript>'),
('<cmake>'),
('<cobol>'),
('<coffeescript>'),
('<common-lisp>'),
('<cql>'),
('<css>'),
('<css3>'),
('<cucumber>'),
('<cython>'),
('<d>'),
('<dart>'),
('<delphi>'),
('<delphi-xe>'),
('<dogescript>'),
('<ecmascript-6>'),
('<elisp>'),
('<elixir>'),
('<elm>'),
('<erlang>'),
('<excel>'),
('<f#>'),
('<factor-lang>'),
('<fish-language>'),
('<flex>'),
('<forth>'),
('<fortran>'),
('<glsl>'),
('<gml>'),
('<go>'),
('<google-apps-script>'),
('<gradle>'),
('<groovy>'),
('<haml>'),
('<haskell>'),
('<haxe>'),
('<hdl>'),
('<hlsl>'),
('<html>'),
('<html5>'),
('<hy>'),
('<idris>'),
('<io-language>'),
('<vb.net>'),
('<vb6>'),
('<vba>'),
('<vbscript>'),
('<j>'),
('<java>'),
('<javascript>'),
('<json>'),
('<jsx>'),
('<julia>'),
('<jython>'),
('<kotlin>'),
('<ksh>'),
('<lc-3>'),
('<less-css>'),
('<lisp>'),
('<logo>'),
('<lolcode>'),
('<lua>'),
('<m4>'),
('<make>'),
('<makefile>'),
('<markdown>'),
('<matlab>'),
('<maxscript>'),
('<nginx>'),
('<nim>'),
('<object-pascal>'),
('<objective-c>'),
('<ocaml>'),
('<octave>'),
('<pascal>'),
('<pascal-script>'),
('<pawn>'),
('<perl>'),
('<perl6>'),
('<php>'),
('<php5>'),
('<postscript>'),
('<powershell>'),
('<processing>'),
('<processing.js>'),
('<progress-4gl>'),
('<prolog>'),
('<purescript>'),
('<pyth>'),
('<python>'),
('<qasm>'),
('<qml>'),
('<r>'),
('<racket>'),
('<razor>'),
('<rc>'),
('<rebol>'),
('<rebol2>'),
('<regex>'),
('<rpython>'),
('<ruby>'),
('<ruby-on-rails>'),
('<rust>'),
('<salesforce-apex>'),
('<sas>'),
('<sass>'),
('<scala>'),
('<scheme>'),
('<scratch>'),
('<scss>'),
('<sh>'),
('<shell>'),
('<siebel-escript>'),
('<simulink>'),
('<sinatra>'),
('<smalltalk>'),
('<sml>'),
('<snobol4>'),
('<sputnik>'),
('<sql>'),
('<swift>'),
('<tcl>'),
('<tex>'),
('<ti-basic>'),
('<typescript>'),
('<unrealscript>'),
('<verilog>'),
('<vhdl>'),
('<vimscript>'),
('<whitespace-language>'),
('<wolfram-mathematica>'),
('<xaml>'),
('<xml>'),
('<xslt>'),
('<yaml>');

SELECT DISTINCT TOP (@NumPosts) p.Id AS [Post Link]
    , p.Tags
    , p.CreationDate AS [Created]
FROM Posts AS p
LEFT JOIN #LanguageTags AS lt
ON p.Tags LIKE '%' + lt.TagName + '%'
WHERE p.ClosedDate IS NULL
    AND lt.TagName IS NULL
    AND p.PostTypeId = 1

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results