Tsql - Excluding The Rows That Matches With Temp Table

I need a help with SQL SELECT query. I have [Details] sql table below. I want to select rows that have matching group id and that does not exist in given temp table DetailID Gro

Solution 1:

You did not connect your nested query with the main table (D).

You can get rid of the join and do this:

SELECT * FROMDetailsDWHERED.GroupIDIN ('A','B','C') ANDNOTEXISTS
   (SELECT 1 FROM @tmpDetails t 
    WHERE  (D.TemplateID IS NOT NULL AND D.TemplateID = t.TemplateID) OR 
           (D.DocumentID IS NOT NULL AND D.DocumentID = t.DocumentID))

Solution 2:

SELECTD.*
FROMDetailsDOUTERAPPLY
    (
        SELECT TOP 11 Match
        FROM @tmpDetails T
        WHERE
            D.TemplateID = T.TemplateID
            OR D.DocumentID = T.DocumentID
    ) TWHERED.GroupIDIN ('A','B','C')
    ANDT.MatchISNULL

Solution 3:

You could use NOT EXISTS and subquery with INTERSECT to handle NULL values:

SELECT * FROMDetailsDWHERED.GroupIDIN ('A', 'B', 'C') 
  ANDNOTEXISTS (SELECT TemplateID, DocumentID FROM @tmpDetails INTERSECT 
                  SELECT D.TemplateID, D.DocumentID)