title: Query to get items which are in list and not in list
body: <p>I have a table 'tbl_Items' with below columns</p>
  
  <pre><code>    [Id] [int] NULL,
      [ItemNo] [varchar](50) NULL,
      [TotalPieces] [int] NULL
  </code></pre>
  
  <p>and another table 'tbl_ItemPieces' with below columns</p>
  
  <pre><code>    [Id] [int] NULL,
      [ItemId] [int] NULL,
      [PieceNo] [int] NULL
  </code></pre>
  
  <p>sample values are like below:</p>
  
  <p>tbl_Items</p>
  
  <pre><code>Id  ItemNo  TotalPieces
  1   1001    5
  2   1002    3
  3   1003    4
  </code></pre>
  
  <p>tbl_ItemPieces</p>
  
  <pre><code>Id  ItemId  PieceNo
  1   1       1
  2   1       2
  3   2       1
  4   2       3
  5   3       3
  6   3       4
  </code></pre>
  
  <p>I have used below query to get count of available pieces and total pieces and available pieces numbers as comma separated string. </p>
  
  <pre><code>    select 
          a.ItemNo, COUNT(b.PieceNo) ActualPieces, a.TotalPieces,    
  
          STUFF((SELECT ', ' + CAST( PieceNo as varchar(50))
          FROM tbl_ItemPieces b 
          WHERE b.itemId = a.Id
          FOR XML PATH('')), 1, 2, '')    
  
      from tbl_Items a
      inner join tbl_ItemPieces b
      on a.Id = b.itemId
      group by a.ItemNo, a.TotalPieces, a.Id
  </code></pre>
  
  <p>which results below </p>
  
  <pre><code>ItemNo  ActualPieces    TotalPieces AvailablePieces
  1001    2                  5            1, 2
  1002    2                  3            1, 3
  1003    2                  4            3, 4
  </code></pre>
  
  <p>I want another column as a comma separated string which contains piece numbers which are not in the table, say if 5 is the total and pieces included are 1,3 then this column value is '2,4,5'</p>
  
  <p>expected result </p>
  
  <pre><code>ItemNo  ActualPieces    TotalPieces  AvailablePieces NotAvailablePieces
  1001    2               5            1, 2            3,4,5
  1002    2               3            1, 3            2
  1003    2               4            3, 4            1,2
  </code></pre>